ConcatRelated Syntax Error (1 Viewer)

kvar · Feb 22, 2019

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 · Feb 22, 2019

Try

Code:

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

Missing " at the end.

kvar · Feb 22, 2019

Code:

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

Same Syntax Error: Missing Operator

theDBguy · Feb 22, 2019

Hi. I think you may be missing a comma.

Code:

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

As an alternative, you could also try simple function.

kvar · Feb 22, 2019

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

Code:

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

Gasman · Feb 22, 2019

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

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

Code:

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

kvar · Feb 22, 2019

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

Gasman · Feb 22, 2019

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

kvar · Feb 22, 2019

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 · Feb 22, 2019

Code:

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

kvar · Feb 22, 2019

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

Code:

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 

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

FROM CopyOfSkus;

theDBguy · Feb 22, 2019

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

Code:

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 · Feb 22, 2019

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.

Code:

SELECT DISTINCT CopyOfSkus.[MDTRegionalCFN], 

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

FROM CopyOfSkus;

theDBguy · Feb 22, 2019

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

arnelgp · Feb 22, 2019

remove the extra spaces and add space to &:

Code:

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

kvar · Feb 22, 2019

The database is attached.

theDBguy · Feb 22, 2019

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

Minty · Feb 22, 2019

Okay here is the working query.

Code:

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 · Feb 22, 2019

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 · Feb 22, 2019

Because you want to group by the first field?

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

ConcatRelated Syntax Error (1 Viewer)

Registered User.

Enthusiastic Amateur

Registered User.

I’m here to help

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

I’m here to help

Registered User.

I’m here to help

..forever waiting... waiting for jellybean!

Registered User.

Attachments

I’m here to help

Attachments

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread