James LaTour
asked on
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"
Prefix10="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
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
PPID - Unique Number format
PPParticipant - String format
PPSurveyNumber - Number format
I have assigned a string variable for the prefix of the fields "PP"
Prefix10="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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Norie,
Your syntax nailed it. Thank you very much. I can't tell you how much time I spent on this. I really appreciate it.
Dale,
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
Your syntax nailed it. Thank you very much. I can't tell you how much time I spent on this. I really appreciate it.
Dale,
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
Questioner indicated suggested solution 'nailed' it.
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.
Dale