Help with dlookup required

  • Thread starter Thread starter Norman Fritag
  • Start date Start date
N

Norman Fritag

Hi there,

I am wonder why this dlookups return null, despite having a record in the
table with a matching date?
Am I missing something here???
Am i running in some limitations, that I am not aware of??

Any hint is much appreciated.

regards

Norman

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & format(txtDatevisited_text,"DD/MM/YYYY")& "#") =
null ??

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & cDate(txtDatevisited_text) & "#") = null

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & (txtDatevisited_text) ) = null

txtDatevisited_text reads in the debugger as "09/02/2004"
Me.Grade = 10
Me.DOHID = 1

DCount("Grade", strTable, "[DOHID] = " & Me.DOHID & " and Grade = " &
Me.Grade ) = 2
In the Record: Dohid = 1;Grade=10,datevisited="25/09/2003"
Dohid = 1;Grade=10,datevisited="09/02/2004"
 
I am wonder why this dlookups return null, despite having a record in the
table with a matching date?
Am I missing something here???
Am i running in some limitations, that I am not aware of??
...
DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & format(txtDatevisited_text,"DD/MM/YYYY")& "#") =
null ??
Click to expand...

Is txtDatevisitet_text declared as a string as the name indicates or does
it contain a valid date? In case it's a date, try:

.... =#" & Format(txtDatevisited_text, "MM\/DD\/YYYY") & "#")

Otherwise check the content of txtDatevisited_text.

HTH - Peter
 
Peter,
Thanks for your reply,
To your question: txtDatevisitet_text is a textbox control declared as a
string and containing "09/02/2004"!
That why I am so puzzled

regards
Norman

Peter Doering said:
I am wonder why this dlookups return null, despite having a record in the
table with a matching date?
Am I missing something here???
Am i running in some limitations, that I am not aware of??
...
DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & format(txtDatevisited_text,"DD/MM/YYYY")& "#") =
null ??
Click to expand...

Is txtDatevisitet_text declared as a string as the name indicates or does
it contain a valid date? In case it's a date, try:

... =#" & Format(txtDatevisited_text, "MM\/DD\/YYYY") & "#")

Otherwise check the content of txtDatevisited_text.

HTH - Peter
Click to expand...
 
Norman,

Dates are assumed to be in the format mm/dd/yyyy, so your date of 09/02/04
is interpreted as 2nd Sept 2004.

I use a format statement similar to

datestring = Format(datevariable, "\#dd-mmm-yyyy\#")

which returns a value of #02-Feb-2004#

This cannot be interpreted incorrectly.

Rod Scoullar
 
To your question: txtDatevisitet_text is a textbox control declared as a
string and containing "09/02/2004"!
Click to expand...

Then try ...

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade & " and [Datevisited] =#" & _
Mid(txtDatevisited_text,4,3) & _
Left(txtDatevisited_text,2) & _
Right(txtDatevisited_text,5) & "#")

HTH - Peter
 
Rod,
thanks for your input.

the system date setting is set to "dd/mm/yyyy" format.
I use the same stringtext value in a vba sub, whereas I evaluate if this
date already exists and it works fine.
You make have noticed that I used (and [Datevisited] =#" &
format(txtDatevisited_text,"DD/MM/YYYY") & "#"). It evaluates null.
I will try your format setting and see if that works.

thanks again

regards Norman
 
thanks Peter'

Will check it out.

Norman

Peter Doering said:
To your question: txtDatevisitet_text is a textbox control declared as a
string and containing "09/02/2004"!
Click to expand...

Then try ...

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade & " and [Datevisited] =#" & _
Mid(txtDatevisited_text,4,3) & _
Left(txtDatevisited_text,2) & _
Right(txtDatevisited_text,5) & "#")

HTH - Peter
Click to expand...
 
Back
Top