Dlookup returning error '3075' data type mismatch

[Vasago]

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)

[Seth Schrock]

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:

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

[Vasago]

Thank you Seth for your response.

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

[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:

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

[Vasago]

Works perfect. Thanks for the help and education.

[Seth Schrock]

No problem. Glad to be able to help.

[Vasago]

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)

[Seth Schrock]

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.