Syntax for multiple criteria for DLookup when using a string variable

I have a table (tblPPData) with the following fields

PPID - Unique Number format
PPParticipant - String format
PPSurveyNumber - Number format

I have assigned a string variable for the prefix of the fields "PP"


Using the Prefix10 string variable

Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant= '" & [Participant] & "'")   The result is correct
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "SurveyNumber = 1")                         The result is correct

What I would like to do is use the Prefix10 string variable using both the Participant and SurveyNumber fields as criteria

The following statement works correctly until I try to use the Prefix10 String variable as I did above for the individual criteria lookups.

Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", PPParticipant= '" & [Participant] & "'" & "And PPSurveyNumber=1") The results are correct

This statement works as well
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & [Participant] & "'" & "And PPSurveyNumber = 1")

As soon as I start trying to use the Prefix10 string variable in place of the prefix for the PPSurveyNumber (Prefix10 & SurveyNumber"=1") I start having issues.

This statement gives me a Run-time error 13 - Type mismatch
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant = '" & [Paticipant] & "'" And Prefix10 & "SurveyNumber= 1")

This statement gives me a Run-time error 3075 - Syntax error (Missing operator) in query expression 'PPParticipant = '1111111111'PPSurveyNumber=1'
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant = '" & [Participant] & "'" & Prefix10 & "SurveyNumber= 1")

I'm at my wit's end. Is there an error in syntax or is this something that just can't be done?

Thank You
James LaTourAsked:
Who is Participating?
NorieVBA ExpertCommented:
Try this.

Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant = '" & [Paticipant] & "' And " & Prefix10 & "SurveyNumber= 1")
Dale FyeCommented:
I have to ask, "why".  Why would you want to embed this Prefix10 in all of those values when you can simply type "PPID", "tblPPData", ...?

Unless you have a whole bunch of tables with similar structure and need to do this for some sort of loop, I can think of no good reason for doing it this way.

James LaTourAuthor Commented:

Your syntax nailed it.  Thank you very much.  I can't tell you how much time I spent on this.  I really appreciate it.


You are spot on.  I am using this in a loop to verify records on various tables with the same name and field structures.

Thank you both for your help

Jim LaTour
NorieVBA ExpertCommented:
Questioner indicated suggested solution 'nailed' it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.