Function returns missing data

[songofsolon]

Greetings,

I have lifted VBA code from the following Knowledge Base Article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;322813

to combine child records in a query. For my application, the SQL then looks like this:

SELECT [EVL].[ProsID], CombineChildRecords("ProsSched","ClassID","ProsID",[ProsID],",") AS CombList FROM EVL

Where:
"EVL" is the parent query
"ProsSched" is the child table
"ClassID" is the data field to be combined
"ProsID" is the link field
with comma as delimeter

And it would work perfectly, if it didn't omit exactly one ClassID from each set. For example, where table ProsSched looks like this:

ProsID ClassID
40 &nbsp&nbsp&nbsp&nbsp 1
40 &nbsp&nbsp&nbsp&nbsp 2
40 &nbsp&nbsp&nbsp&nbsp 3
41 &nbsp&nbsp&nbsp&nbsp 2
41 &nbsp&nbsp&nbsp&nbsp 5

The query will return this:
40 &nbsp&nbsp&nbsp&nbsp 2,3,
41 &nbsp&nbsp&nbsp&nbsp 5,

including that trailing comma. If I were to add record "41,1" to ProsSched, the query would then return "2,5," for ProsID 41.

Obviously this is a cut-and-paste job, and I am just beginning to learn the very basics of VBA, so I would really appreciate any advice you could give. Thank you!

 

[AncientOne]

The Microsoft code is slightly in error in respect of the delimiter. In the final SQL string which you paste into the query window, there should be a space after the comma, because the code truncates the last two characters in the string.

That doesn't explain your problem though. In your case, it obviously isn't truncating the string because the comma is left behind.

Do these:

1) don't use a delimiter in the SQL for the query, let it use the default semicolon and see if that makes any difference.

2) add to the code:
Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close
MsgBox varResult

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)

CombineChildRecords = varResult

MsgBox varResult


These will enable you to inspect the string both before and after it's truncated.


Post your findings, along with the code and the Query's SQL. You probably have a tiny typo somewhere.

 

[songofsolon]

Thank you Ancient One!

The lack of a space after the comma delimeter was precisely the problem. Without it, the return for 40 was "3,2,1," before truncating and "3,2," after, so it was deleting the final comma and the character preceding it. Putting the space in fixed it. It would have taken me ages to figure that out.

Thanks also for the "MsgBox varResult" tip, very useful.

Cheers!