Concatenating a Crosstab query

[Jeanette]

Hello
I am reiterating my message posted 12/17/01;I did not get response to it. Is there anyway to concatenate the results of a crosstab query without using the "&". I tried writing an expressing using the Join function in the expression builder, but I get an error message saying "undefined function 'Join' in expression". I am not a programmer and my knowledge of VBA language is minimal.

 

[Pat Hartman]

The & (and sometimes the +) is used to concatenate two fields. "Join" is used in a query to link two tables together based on matching key values. It is not used in expressions. What exactly are you trying to do?

 

[Jeanette]

I created a crosstab query based on a field called CB that currently has 17 different community boards thereby creating 17 columns. I need to have the values in the columns concatenated and separated by a comma into one field that can be used in a report. Also, this crosstab query has potential to have an infinite number of columns because it will reflect new community boards as they are added. I can concatenate the columns with the "&" but this is too cumbersom and does not automatically update new community boards that are added to the CB Field. In effect, would have to concatenate a new column each time a new one is added. I need the concatenated field to automatically update. I hope this is not too confusing.

 

[Pat Hartman]

I think that rather than making a crosstab you would be better off using a subreport with multiple columns. The subreport would use a standard select query and it wouldn't matter how many community boards you end up with. I don't know how long each value can be but you could easily exceed 255 characters if your list grows too large.

 

[Jeanette]

Thanks. I'll try your suggestion.