ConcatRelated Syntax Error

[kvar]

I'm using Allen Brown's ConcatRelated function. In my query I get a syntax error for a missing operator. I have tried every possible combination of quotation marks, brackets, single quotes etc imaginable and can not seem to get it to work. I got incorrect results with a couple of my attempts but just can't get what I need.
Any ideas would be appreciated!! (All are text fields)

My current query:
SELECT MDTRegionalCFN
ConcatRelated("Region","Copy of Skus","MDTRegionalCFN = '" & [MDTRegionalCFN] & "' )
FROM CopyOfSkus;

 

[Gasman]

Try

SELECT MDTRegionalCFN
ConcatRelated("Region","[Copy of Skus]","MDTRegionalCFN = '" & [MDTRegionalCFN] & "'") 
FROM CopyOfSkus;

Missing " at the end.

 

[kvar]

SELECT MDTRegionalCFN
          ConcatRelated("Region","[Copy of Skus]","MDTRegionalCFN = '" & [MDTRegionalCFN] & "'" ) 
FROM CopyOfSkus;

Same Syntax Error: Missing Operator

 

[theDBguy]

Hi. I think you may be missing a comma.

 SELECT MDTRegionalCFN,
    ConcatRelated("Region","[Copy of Skus]","MDTRegionalCFN = '" & [MDTRegionalCFN] & "'" )  
FROM CopyOfSkus;

As an alternative, you could also try simple function.

 

[kvar]

I was missing a comma! Thank you.
Progress I think, now I get an error that says Data type mismatch in criteria expression.

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 
             ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= " & [MDTRegionalCFN]) AS Regions
FROM CopyOfSkus;

 

[Gasman]

You said they were all text?, so you need the single quotes for [MDTRegionalCFN] ?

I was missing a comma! Thank you.
Progress I think, now I get an error that says Data type mismatch in criteria expression.

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 
             ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= " & [MDTRegionalCFN]) AS Regions
FROM CopyOfSkus;

 

[kvar]

I'm not sure what you mean.
Should I replace the " with '?

 

[Gasman]

Look at my first post to you with regard to the variable [MDTRegionalCFN]

 

[kvar]

When I add the quotes back as we had them I get the syntax error missing operator again and it highlights the last set of quotation marks. The " ' " part.

 

[arnelgp]

ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= [COLOR="Red"]'[/COLOR]" & [MDTRegionalCFN] & "[COLOR="Red"]'[/COLOR]") AS Regions

 

[kvar]

This gives me syntax error missing operator and highlights the " ' " at the end.

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 

             ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= ' " & [MDTRegionalCFN]" ' ") AS Regions

FROM CopyOfSkus;

 

[theDBguy]

This gives me syntax error missing operator and highlights the " ' " at the end.

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 

             ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= ' " & [MDTRegionalCFN]" ' ") AS Regions

FROM CopyOfSkus;

Hi. What happens if you copy and paste the code I posted in post #4? I'm just curious if you'll get an error using it. You are missing an ampersand (&) this time, and you should remove the extra spaces around the single quote.

 

[kvar]

Spaces were just for the post to make it easier to see.
The following takes about 3 minutes to run and "Regions" is just blank for every record.

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 

             ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= ' " & [MDTRegionalCFN]&" ' ") AS Regions

FROM CopyOfSkus;

 

[theDBguy]

Can you post a small copy of your db with test data?

 

[arnelgp]

remove the extra spaces and add space to &:

ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= [COLOR="Navy"]'[/COLOR]" & [MDTRegionalCFN] & "[COLOR="Navy"]'[/COLOR]") AS Regions

 

[kvar]

The database is attached.

 

[theDBguy]

Hi. Thanks. You did have spaces in your code in your db. Check out Query2 in the attached updated copy of your db.

 

[Minty]

Okay here is the working query.

SELECT DISTINCT CopyOfSkus.MDTRegionalCFN, ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= '" & [MDTRegionalCFN] & "'") AS Regions
FROM CopyOfSkus;

Adding indexes to the two fields being used mean its runs in about 6 seconds on my ancient underpowered laptop....


(I may have lied i7 vPro 32Gb ram M2 SSD)

 

[kvar]

DBGuy, that works. Thank you for the solution!

However it seems odd that you would have to make a select distinct query and then pull the data from that. I can't see a reason why you can't just pull from the table directly. I guess it doesn't matter as long as it works, just irritates me I can't find a simpler solution.

Thank you!!

 

[Minty]

Because you want to group by the first field?

If you don't you just get all your records back...?