DLookup Date Issues

[Guest]

Please help, I am trying to use a pop up form that will return some data 2
hours back in a table from text boxes within the form. However the DLookup
for the Date/Time field returns nulls most of the time (even when the data is
there)

'Returns the date/time 2 hours back of selection (works fine)
[G1RT-2] =Format(DateAdd("h",-2,[G1RT]),"yyyy mm dd hh:nn:ss")

'This is the lookup that fails- returns null (again not always), trying to
lookup the data from previous box
=DLookUp("[Pres1]","Gradient-Gauge1","[RealTime1] = #" &
Format([G1RT-2],"yyyy mm dd hh:nn:ss") & "#")

The date/time is in the table but only returns values on certain "times"

*Please note that the date/time field im looking up, is set as text (for
reasons i cannot any longer recall. but was necessary) Perhaps this is the
problem? should i be looking up another way based on this?

Please help

 

[John Vinson]

Please help, I am trying to use a pop up form that will return some data 2
hours back in a table from text boxes within the form. However the DLookup
for the Date/Time field returns nulls most of the time (even when the data is
there)

'Returns the date/time 2 hours back of selection (works fine)
[G1RT-2] =Format(DateAdd("h",-2,[G1RT]),"yyyy mm dd hh:nn:ss")

'This is the lookup that fails- returns null (again not always), trying to
lookup the data from previous box
=DLookUp("[Pres1]","Gradient-Gauge1","[RealTime1] = #" &
Format([G1RT-2],"yyyy mm dd hh:nn:ss") & "#")

The date/time is in the table but only returns values on certain "times"

*Please note that the date/time field im looking up, is set as text (for
reasons i cannot any longer recall. but was necessary) Perhaps this is the
problem? should i be looking up another way based on this?

Since the field is text, use ' rather than # as the delimiter. Access
is trying to convert it to date and back to text to search RealTime1.

John W. Vinson[MVP]

 

[david epsom dot com dot au]

Access/VB stores date/time as a floating point number,
with the integer being the date part, and the fraction
being a fraction of a day.

You will ALWAYS get rounding errors converting from
seconds to fractional days and from fractional days
to seconds.

To avoid rounding errors with floating point comparisons,
you should alway use ranges, rather than exact values.

perhaps something like this:

sDateRange= "([RealTime1]>" & sTime1 & _
") and ([RealTime1]<" & sTime2 & ")"


=DLookUp("[Pres1]","Gradient-Gauge1",sDateRange)