Short trip: How do I build a dynamic SQL/query code: [1] & ", " & [2] & ", " ~~~~~?
Scenic View: For one request (RequestID] there can be many test methods (numbered sequentially by the program) and many samples (also numbered sequentially by the program).
BUT, each test method is assigned a certain set of samples. For example:
TestMethod# ..... Sample#
1 ....................... 1
1 ....................... 3
1 ....................... 9
2 ....................... 1
2 ....................... 6
2 ....................... 7
3 ....................... 9
DESIRED RESULT: via SQL/query I want to present the data as:
TestMethod# ..... Assigned
1 ....................... 1, 3, 9
2 ....................... 1, 6, 7
3 ....................... 9
.
Sooo, I created a crosstab query:
[TestMethod] ..... [1] .. [2] .. [3]
1 ....................... 1 .....3 ..... 9
2 ....................... 1 .... 6 ..... 7
3 ....................... 9
.
.
.
I built a couple of aggregate queries to find the [TestMethod] having the most samples and named the field [MaxCode]. For the preceding example, [MaxCode] = 3
Everything works up to this point. My plan was to build a SELECT query with a field entitled [Assigned].
In the query wizard, the field would look like: Assigned: [1] & ", " & [2] ", " & ~~~~~~ [9]
PROBLEM: I built a function in a module I thought would create the preceding string -- it doesn't work:
Public Function BuildAssignments(MaxCode) As String
Dim m As Integer
Dim f As String
Dim Assigned As String
Dim i As Integer
m = MaxCode
f = "[" & i & "], "
i = 0
Do Until i > m
Assigned = Assigned & f
i = i + 1
Loop
End Function
Because the result would look like (eg for [TestMethod]= 3): , , , , , , , , 9
I would build a query to delete ", ,"
What am I doing wrong?