By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,939 Members | 1,684 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,939 IT Pros & Developers. It's quick & easy.

Dlookup returning error '3075' data type mismatch

P: 46
Trying to look up a value based on 2 criteria. Can't seem to debug the error thanks for your help!


[Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "And [Date]='" & Date & "'"), 0)
Mar 12 '14 #1

✓ answered by Seth Schrock

Ah, something else that I missed. You will need to add an ending single quote and a space before the word and in your criteria. So it would be like this:
Expand|Select|Wrap|Line Numbers
  1. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#"), 0)

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
The problem is your [Date] field criteria. You are using single quotes, but you need to use # instead. So it would be something like this:
Expand|Select|Wrap|Line Numbers
  1. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "And [Date]=#" & Date & "#"), 0)
Mar 12 '14 #2

P: 46
Thank you Seth for your response.

After changes I am getting run-time error '3075'
Syntax error in string in query expression 'Employee='Batson,Leroyand [Date]=#3/12/2014'.
Mar 12 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Ah, something else that I missed. You will need to add an ending single quote and a space before the word and in your criteria. So it would be like this:
Expand|Select|Wrap|Line Numbers
  1. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#"), 0)
Mar 12 '14 #4

P: 46
Works perfect. Thanks for the help and education.
Mar 12 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
No problem. Glad to be able to help.
Mar 12 '14 #6

P: 46
Seth do you mind taking a look at one more. I realized I needed a third criteria. Here is the code with added criteria.

[Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & "' And [Trip Sign On]=0"), 0)
Mar 12 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
Only one question is allowed per thread, but I'll give you a bit of direction as it is kind of related. Drop the single quote before your second and. If you continue to have issues with this, please start a new thread and I or someone else will continue to help you.

I just saw that you did post a new thread. I'll post over there as well.
Mar 12 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.