Concatenate strings from different records

[Guest]

I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/modules/mdl0008.htm

 

[Guest]

I found Duane Hookum's link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and got it
going. But I am getting an error message: Syntax error in query expression
'PIN=$TMPTst1'

The query expression i am using is:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=" & [PIN])

Is there a problem with the dollar sign? If so how do I get around it?

Thanks,
Jonathan

 

[Guest]

I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan

 

[Duane Hookom]

Since your PIN field is text, you must delimit it with quotes:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=""" & [PIN]
& """")

--
Duane Hookom
MS Access MVP

I found Duane Hookum's link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and got
it
going. But I am getting an error message: Syntax error in query expression
'PIN=$TMPTst1'

The query expression i am using is:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=" &
[PIN])

Is there a problem with the dollar sign? If so how do I get around it?

Thanks,
Jonathan

I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the
user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan

 

[Guest]

Thanks everyone got it working.

Since your PIN field is text, you must delimit it with quotes:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=""" & [PIN]
& """")

--
Duane Hookom
MS Access MVP

I found Duane Hookum's link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and got
it
going. But I am getting an error message: Syntax error in query expression
'PIN=$TMPTst1'

The query expression i am using is:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=" &
[PIN])

Is there a problem with the dollar sign? If so how do I get around it?

Thanks,
Jonathan

I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the
user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan