Dlookup with a function in the where statement

[nigelbloomy]

I am having syntax errors using a dlookup with a function in the where section. I have a field with a 4 digit account number [CECODE]. I need to match it with a field that has only the first two digits followed by "xx"[Cost Account]. All of the fields involved are text fields.

I have the following formula typed into the query: DLookUp("[Reporting Account]","2 Digit Reporting","[Cost Account]= " & Left([CECODE],2) & 'xx')


I get the following error: Syntax error (Missing Operator) in query expression '[Cost Account]=63xx'

I am a little confused because the error result looks exactly like I would expect. The text in [Cost Account] is 63xx so it seems like everything should be great. What is my missing operator?

[pbaldy]

Try

DLookUp("[Reporting Account]","2 Digit Reporting","[Cost Account]= '" & Left([CECODE],2) & "xx'")

[nigelbloomy]

It works perfectly. I hadn't thought of putting the xx between the double quote and single quote. The syntax for the dlookup can be confusing. Thank you for figuring it out for me.

[pbaldy]

No problem. This may help you in the future:

DLookup Usage Samples