Findfirst for date

[vcsphx]

All,

I have been getting some sporadic errors when I use the Findfirst for the
date fields. Sometimes it works fine and at other times, it doesn't. For
example, I had these two records in my table "tbltest" for the field
"Start_time": "5/6/2009 10:08:00 PM" and "5/6/2009 11:01:12 PM". This is the
code I have:

tbltest.findfirst("Start_time=#" & Format(rsqry!start_time, "mm/dd/yyyy
hh:mm:ss AM/PM") & "#)

When the code searches for "5/6/2009 10:08:00 PM", it captures the
corresponding record in the table and returns "False" for the
"recordset.nomatch". But when it seraches for "5/6/2009 11:01:12 PM", though
there is a record with the same timestamp under the "Start_time" column,
still I get "True" for the "recordset.nomatch". Does any of you know why it's
happening this way?

Note: The "Start_time" field is defined as "General Datetime" in the table.

Thanks a ton for your help!

 

[John W. Vinson]

All,

I have been getting some sporadic errors when I use the Findfirst for the
date fields. Sometimes it works fine and at other times, it doesn't. For
example, I had these two records in my table "tbltest" for the field
"Start_time": "5/6/2009 10:08:00 PM" and "5/6/2009 11:01:12 PM". This is the
code I have:

tbltest.findfirst("Start_time=#" & Format(rsqry!start_time, "mm/dd/yyyy
hh:mm:ss AM/PM") & "#)

When the code searches for "5/6/2009 10:08:00 PM", it captures the
corresponding record in the table and returns "False" for the
"recordset.nomatch". But when it seraches for "5/6/2009 11:01:12 PM", though
there is a record with the same timestamp under the "Start_time" column,
still I get "True" for the "recordset.nomatch". Does any of you know why it's
happening this way?

Note: The "Start_time" field is defined as "General Datetime" in the table.

Thanks a ton for your help!

This may be another instance of a problem that's come up a few times recently
in this group. A Date/Time value is stored as a Double Float number, accurate
to microseconds - but you can only display (or search for) a time precise to
integer seconds:

?cdbl(#5/6/2009 11:01:12 PM#)
39939.9591666667
?cdate(39939.9591666667)
5/6/2009 11:01:12 PM
?cdate(39939.9591666680)
5/6/2009 11:01:12 PM
?cdate(39939.9591666600)
5/6/2009 11:01:12 PM

Just to see the range:

?cdbl(#5/6/2009 11:01:11 PM#)
39939.9591550926
?cdbl(#5/6/2009 11:01:12 PM#)
39939.9591666667
?cdbl(#5/6/2009 11:01:13 PM#)
39939.9591782407

So you can have a stored date/time that ie exactly the same (11:01:12 PM) as
far as appearance or searching is concerned, but is in fact not a match,
because of differences past the fourth decimal place.

My inclination would be to use a fuzzier search; either

tbltest.findfirst "Format(Start_time, ""mm/dd/yyyy hh:mm:ss AM/PM"")
=Format(rsqry!start_time, ""mm/dd/yyyy hh:mm:ss AM/PM"")

which will unfortunately not use any index on StartTime, or search for a
one-second range on either side of the query time.