date in criteria question

[Mark]

Hello all,

I have a dlookup that I keep getting a “Data mismatch†error. I’m using a
date in my criteria and this seems to be the culprit.

Dim vTime as Date

vTime = Date()
vRst = dolookup(“[id]â€, “tbProdQueâ€, “[cktime] = “â€â€ & vTime & “â€â€)
..
My cktime is a date/time field.

 

[Stefan Hoffmann]

hi Mark,

I have a dlookup that I keep getting a “Data mismatch†error.

A DLookup() can return NULL, which can only be stored in a variable of
type Variant (vRst is declared?).

I’m using a date in my criteria and this seems to be the culprit.

Yes, this is true in your case.

Dim vTime as Date
vTime = Date()
vRst = dolookup(“[id]â€, “tbProdQueâ€, “[cktime] = “â€â€ & vTime & “â€â€)

You need a date literal in your condition:

vRst = DLookup("[ID]", _
"tbProdQue", _
"[cktime] = " & _
Format(DateTime, "\#m\/d\/yyyy hh\:nn\:ss\#"))

But be aware of the date and time part when comparing date/time values.


mfG
--> stefan <--

 

[John W. Vinson]

Hello all,

I have a dlookup that I keep getting a “Data mismatch” error. I’m using a
date in my criteria and this seems to be the culprit.

Dim vTime as Date

vTime = Date()
vRst = dolookup(“[id]”, “tbProdQue”, “[cktime] = “”” & vTime & “””)
.
My cktime is a date/time field.

The correct delimiter for dates is # - you're using ” (a "smart quote" which
isn't even recognized for text fields or string delimiters.

Try

vRst = DLookup("[id]", "tbProdQue", "[cktime] = #" & vTime & "#")

Note also that if your computer's regional settings use a European dd/mm/yyyy
format, you must recast it into either an American mm/dd/yyyy format, or an
unambiguous format such as yyyy.mm.dd:

vRst = DLookup("[id]", "tbProdQue", "[cktime] = #" &
Format(vTime,"yyyy\.mm\.dd") & "#")

John W. Vinson [MVP]