Dlookup error 13

  • Thread starter Thread starter Kman
  • Start date Start date
K

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\#"))
 
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
 
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
 
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().
 
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
 
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#)
 
Kman said:
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
Click to expand...
 
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.
 
Back
Top