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

Dloopup Run Time error '3075' data type Mismatch

P: 46
Can't seem to correct the last 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 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
You have an extra single quote before the second and. The reason you needed it before the first AND was because you needed to close the single quote that you had (and needed) after the Employee='. You also don't need to split up the ending hash mark from the rest of your criteria. So something like this should work fine (added a little change to make troubleshooting easier).
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2. strCriteria = "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "# And [Trip Sign On]=0"
  3. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
Mar 12 '14 #2

Expert 100+
P: 1,240
It's a really bad idea to name a column, in a table or in a query, "Date". That's because "Date" is already a keyword as you demonstrate in your code, calling the Date function.

I think if you change the & Date to
Expand|Select|Wrap|Line Numbers
  1. Format(Date(),'MM/DD/YY')
it will work.

Mar 12 '14 #3

Post your reply

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