Use of apostrophes vs. quotation marks in functions

[ManLabor]

I am trying to use the ConcatRelated() function and am having problems passing the "Where" argument when I use a value from a form.

ex ConcatRelated('[txtError]','[tblWorkload]','[tblWorkload].[Analyst] like [AnalystNameonForm]','',' Delimiter').

My first question is why I only can seem to get the function to work if each is enclosed by an apostrophe. I see the examples always use quotation marks. I have the same issue with builtin functions like Dlookup.



Secondly, I can get this to work if I type in the [AnalystNameonForm] surrounded by quotes like the example, but if I try to use the value of the field, I get errors for too few arguments. I can also get this to work if I put the above argument directly into the module.

Thanks for any help

[June7]

LIKE operator is only useful with wildcard, otherwise just use = sign.

Must concatenate variables. Reference to field/control as parameter is a variable.

Not sure why the issue with quote mark. Where are you calling the function?

Is this Allen Browne's code? http://allenbrowne.com/func-concat.html

What is the separator you want - comma, colon, semi-colon?

I prefer apostrophe as text delimiter instead of quote pair (same for domain aggregate functions like DLookup).

If you are calling from a textbox on form or report and want comma separator, this should work:

ConcatRelated("[txtError]","[tblWorkload]","[Analyst] ='" & [Analyst] & "'")

Really should use analyst unique ID as criteria instead of name.

You don't show any order criteria.

[ManLabor]

Yes this is using Allen Browne's code.

If I replace Allen's code for the string variable, the function works correctly.

strSql = strSql & " WHERE " & "[Workload and Error]![Analyst] Like " & Chr(34) & "*" & Forms![Workload Review Form]![analyst 5] & "*" & Chr(34)

Note: this is with [analyst 5] = "initials"

This is the result from debug.print strSQL:

SELECT [Workload and Error]![Error 1] FROM [Workload and Error] WHERE [Workload and Error]![Analyst] Like "*Initials*"

If I use the following as the control source on a textfield in a Report:

=ConcatRelated('[Workload and Error]![Error 1]','[Workload and Error]','[Workload and Error]![Analyst] like " & chr(34) & "*" & Forms![Workload Review Form]![Analyst 5] & "*" & chr(34)','',' // ')

This is the result from debug.print strSQL

SELECT [Workload and Error]![Error 1] FROM [Workload and Error] WHERE [Workload and Error]![Analyst] like " & chr(34) & "*" & Forms![Workload Review Form]![Analyst 5] & "*" & chr(34)

This gives the error 3075, Syntax error in string in query expression '[Workload and Error]![Analyst] like " chr(34) & "*" & Forms![Workload Review Form]![Analyst 5] & "*" chr(34)'
Not sure why the issue with quote mark. Where are you calling the function? This is from the control source of a text field on a Report. I will have to keep searching on this.


I prefer apostrophe as text delimiter instead of quote pair (same for domain aggregate functions like DLookup). I used "//" as the delimiter. I was was doing the original post from memory.If you are calling from a textbox on form or report and want comma separator, this should work:

ConcatRelated("[txtError]","[tblWorkload]","[Analyst] ='" & [Analyst] & "'")

You don't show any order criteria. No sorting is really needed for this application.