Allan Browne's ConcatRelated help

[BillMcD37]

I'm using Allan Browne's ConcatRelated module--well, trying to use it.

I absolutely cannot get it to work without the following error" Error 3075: Syntax error (Missing Operator) in query expression 'Project ID = PD00000914'.

Here's what I'm typing in the query:

expr1: ConcatRelated("E-mail","POC Master","Project ID = " & [New Program Sponsor POC].[Project ID])

I've tried double quotes "", triple quotes """ and quadruple quotes """"".

What am I missing?:banghead:

 

[pbaldy]

Try

expr1: ConcatRelated("E-mail","POC Master","Project ID = '" & [New Program Sponsor POC].[Project ID] & "'")

 

[BillMcD37]

Thanks.

I made your suggested changes. One error went away and one new one came up.

Error 3131 Syntax error in FROM clause...

Here's my SQL code:

SELECT [test email table].[Project Definition], [test email table].Description, [test email table].[E-mail], ConcatRelated("E-mail","test email table","Project ID = '" & [test email table].[Project ID] & "'") AS expr1
FROM [test email table];

 

[pbaldy]

Well, your table and field names contain inadvisable spaces and symbols, so likely need to be bracketed.

 

[BillMcD37]

Once I removed the spaces from my field names, the function works perfectly. Thanks!

 

[pbaldy]

Happy to help! I think you'll be happier without the spaces in the long run.

 

[ma t]

I'm trying to use this as well, and I must be doing something wrong. I receive the error "Undefined Function 'ConcatRelated' in Expression. This is my sql-
SELECT [ImmunizationInfoTbl].[PID], [ImmunizationInfoTbl].VaccineID, [ImmunizationInfoTbl].[VaccineDate], ConcatRelated("VaccineDate","ImmunizationInfoTbl","VaccineID = " & [ImmunizationInfoTbl].[VaccineID]) AS expr1
FROM [ImmunizationInfoTbl];

Help Please!

 

[pbaldy]

Based on the error, either you haven't copied the function into a standard module, or you named the module the same as the function.

 

[ma t]

Thanks! Who knew you couldn't name the module the sames as the function!
Now, on to the next question, I have this working without errors, but ALL of the vaccine dates are showing up for All vaccine id. Help again Please?
I am trying to produce a row with PID (person id), VaccineID, and then all of the dates for that PID and Vaccine ID in a concatenated field...
Thanks in advance!

 

[pbaldy]

Sounds like you need both in the criteria, like:

"VaccineID = " & [ImmunizationInfoTbl].[VaccineID] & " And PID = " & [PID]

 

[ma t]

Well, I tried that and I am still getting all of the dates for all of the vaccines...
this is what i have as the control source of a text box in a form...
=ConcatRelated("VaccineDate","ImmunizationInfoTbl","PID = " & [PID] And "VaccineID = " & [VaccineID])
The source of the form is the ImmunizationInfoTbl with the fields PID, VaccineID and VaccineDate.

 

[pbaldy]

Look at the quotes and ampersands I have versus yours; yours are off.

 

[ma t]

I get #Name? using your version...

=ConcatRelated("VaccineDate","ImmunizationInfoTbl","VaccineID = " & [ImmunizationInfoTbl].[VaccineID] & " And PID = " & [PID])

Thank you for helping!

 

[pbaldy]

Can you post the db here?

 

[ma t]

yes- give me 10 minutes...

 

[ma t]

Done! Thank you!

 

[pbaldy]

Something is corrupted or something; it's not recognizing the function. I imported the relevant objects into a new db and this worked in the query:

Expr1: ConcatRelated("VaccineDate","ImmunizationInfoTbl","VaccineID = " & [VaccineID] & " And PID = " & [PID])

You might try importing all of your objects into a new db and try it again.

 

[ma t]

wow! Thanks, I am not sure what the problem was, but yes, its working now! Thank you so much!

 

[pbaldy]

No problem!

 

[ma t]

THANK YOU!!!
Following your example i was able to add VaccineType" to my statement and it WORKS!!!!