Dlookup error 13

[Kman]

Hi,

I am trying to validate that a record doesn't exist using a dlookup function
based on Date and EmployeeID in a table that has only three fields; DateIn,
EmplyeeID and TimeIn, but I get a runtime "error 13 - type mismatch". Can
anyone help? Here is the sample of code where the runtime error occurs:

varResult = DLookup("TimeOn", "TimeOn", EmployeeID =
Forms!ClockOn!EmployeeID And "DateIn = " & Format(Forms!ClockOn!DateIn,
"\#mm\/dd\/yyyy\#"))

 

[Allen Browne]

The Criteria string is not correct. You need quotes before EmployeeID.

Also, the string will be mal-formed if either DateIn or EmployeeID is null.

Try something like this:

Dim strWhere As String
If IsDate(Forms!ClockOn!DateIn) AND Not IsNull(Forms!ClockOn!EmployeeID)
Then
strWhere = "(EmployeeID = " & Forms!ClockOn!EmployeeID & _
") AND (DateIn = " & Format(Forms!ClockOn!DateIn,
"\#mm\/dd\/yyyy\#") & ")"
varResult = DLookup("TimeOn", "TimeOn", strWhere)
End If

If that still yields error 13, perhaps EmployeeID is Text and not Number. If
so, you need extra quote marks:
http://allenbrowne.com/casu-17.html

 

[Kman]

Hi Allen,

Thanks for your assistance. I am getting further with the process than
before, however I am now getting a run time error 3464 - Data type mismatch
in criteria expression. Could it be that the date in the table is formatted
as medium date?
Peter

 

[Allen Browne]

The formatting in the table is not the problem. What does matter is that it
is a Date/Time field in your table (not a Text field.)

And what data type is the EmployeeID? Number or Text (in table design.)

Add the line:
Debug.Print strWhere
When it fails, press Ctrl+G to open the Immediate Window, and see what came
out. It should look something like this:
(EmployeeID = 99) AND (DateIn = #12/31/2009#)

Adjust until you get it right.

If you are still stuck, try with just the EmployeeID, or just the DateIn to
see which one is giving the problem.

BTW, I am assuming that the table is named TimeOn, and it has a field with
the same name. That's what you've asked for in your DLookup().

 

[Kman]

Hi Allen,

I have tried splitting the criteria in two and I have success with each set
of criteria individually. The Runtime error 13 happens when I join the
criteria. BTW the EmployeeID is a text field and I think that is where the
problem lies. I have tried to modify the syntax to suit a string expression
and as I mentioned before I have success individually but not as a citeria
with two expressions. Can you please help further?
Thanks Peter

 

[Allen Browne]

To achieve that:

strWhere = "(EmployeeID = """ & Forms!ClockOn!EmployeeID & _
""") AND (DateIn = " & _
Format(Forms!ClockOn!DateIn, "\#mm\/dd\/yyyy\#") & ")"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

Use the Debug.Print to get it right. It will need to look like this:
(EmployeeID = "99") AND (DateIn = #12/31/2009#)

 

[beatriz]

Hi Allen,

I have tried splitting the criteria in two and I have success with each
set
of criteria individually. The Runtime error 13 happens when I join the
criteria. BTW the EmployeeID is a text field and I think that is where
the
problem lies. I have tried to modify the syntax to suit a string
expression
and as I mentioned before I have success individually but not as a citeria
with two expressions. Can you please help further?
Thanks Peter

 

[Kman]

Hi Allen,

Thank you so much for helping me with this. The usage of quotes to derive
the correct syntax is confusing for a novice.