concatenation query

[Rich M]

Hello All
I'm trying to make a cocatenation query using Allen Browne's function. I can't post the link as this is my first post sorry.

The query is this
SELECT qryMerge.FileName, qryMerge.Length, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID],"ActorsID = " & [ActorsID]) AS Categories
FROM qryMerge;


can someone plz tell what's wrong.

 

[pbaldy]

Are you trying to filter on two fields? If so that syntax is wrong. Try:

SELECT qryMerge.FileName, qryMerge.Length, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories

 

[Rich M]

thanks pbaldy for the quick reply. Yes i'm trying to filter on two field it concatenates the categories field.

before :
forrest gump comedy
forrest gump romance

Now:
forrest gump comedy,romance
forrest gump comedy,romance

would like:
forrest gump comedy,romance

 

[pbaldy]

Try this:

SELECT qryMerge.FileName, qryMerge.Length, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
GROUP BY qryMerge.FileName, qryMerge.Length

 

[Rich M]

SELECT qryMerge.FileName, qryMerge.Length, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
GROUP BY qryMerge.FileName, qryMerge.Length
FROM qryMerge;

tried this but tells me this.
The SELECT statement includes a reserved word or argument name that is misspelled or missing or the punctuation in incorrect. it highlights GROUP

 

[pbaldy]

My mistake, didn't notice the FROM was missing from yours. Try

SELECT qryMerge.FileName, qryMerge.Length, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
FROM qryMerge
GROUP BY qryMerge.FileName, qryMerge.Length

 

[Rich M]

again thanks pbaldy for you quick response but something is still not right.

it saying the LEVEL clause includes a reserved word or argument name that is misspelled or missing or the punctuation in incorrect.

 

[pbaldy]

Wasn't in your example, try without:

SELECT qryMerge.FileName, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
FROM qryMerge
GROUP BY qryMerge.FileName

 

[Rich M]

SELECT qryMerge.FileName, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
FROM qryMerge
GROUP BY qryMerge.FileName

ok for some reason it's not liking the GROUP BY. It still says LEVEL clause includes a reserved word or argument name that is misspelled or missing or the punctuation in incorrect.

 

[pbaldy]

Can you attach the db here?

 

[Rich M]

ok I attached the database. i'm sorry for being such a pain.

 

[Rich M]

ok go it to work. thank you pbaldy for your help

here's the query.
SELECT qryMerge.FileName, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]) AS Categories
FROM qryMerge
GROUP BY qryMerge.FileName, ConcatRelated("Categories","qryMerge","ClipID = " & [ClipID] & " AND ActorsID = " & [ActorsID]);

 

[pbaldy]

Sorry, I was on the road yesterday. Glad you got it sorted out.