FindFirst and query not finding records - date values

[rivka.howley]

I wrote some code that creates a table with a date/time field at
15-minute intervals. Here's how I create and populate the table

With tblDataTemp

..Fields.Append .CreateField("CT_ID", dbLong)
..Fields.Append .CreateField(strTmpIDFld, dbLong)
..Fields.Append .CreateField(strTmpDateFld, dbDate)
..Fields.Append .CreateField(strTmpDataFld, dbDouble)

End With
..
..
..
dtAdd = AdjustTime(dtStart)
Do While dtAdd < dtEnd + 1 + 15 / 1440
rstDataTemp.AddNew
rstDataTemp.Fields(strTmpDateFld).Value = dtAdd
rstDataTemp.Update
dtAdd = dtAdd + 15 / 1440
Loop

I then use the FindFirst method to search for a record with a specific
date/time. Here is that line in the code:

rstDataTemp.FindFirst ("[" & strTmpDateFld & "]" & "=#" + strAdjDate +
"#")

The problem is, it only works for times up to 4/18/2005 15:30 (this
happens to be the first 3 records, but I have tried deleting those
first 3 records and then it finds nothing). I have stopped the code,
copied and pasted the value of strAdjDate into a query, and it doesn't
find the record. I have even copied and pasted a value directly from
the table I created into the criteria of the query builder and it
*still* doesn't return the record.

I have some similar code that creates records with date/time (but one
at a time, it reads the dates from another table then writes to this
table), and I use a FindFirst on that and it works fine:

rstData.FindFirst ("[" & strDateFld & "]" & "=#" + theAdjDate + "# AND
[" & strIDFld & "] = " & Trim(Str(numID)))

So I'm thinking it has to do wtih the way I'm creating the table in the
first place?? Anyone have any idea?

Thanks
Rebecca

[Allen Browne]

This problem is an example of the impresicion of floating point numbers.

The Date/Time data type in access is stored as a real number, where the
integer part represents the date and the fractional part the time of day:
0.5 = noon; 0.25 = 6am, and so on. Unfortunately, binary numbers (and even
decimal numbers) cannot represent the times precisely. It is very common to
not get exact matches, and if you add fractions in a loop the approach
doomed.

You could use an integer loop controller to avoid the increasing error,
e.g.:
For i = 0 to 95
rstDataTemp.AddNew
rstDataTemp.Fields(strTmpDateFld) = DateAdd("m", i * 15, dtStart)
rstDataTemp.Update
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ri**********@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I wrote some code that creates a table with a date/time field at
15-minute intervals. Here's how I create and populate the table

With tblDataTemp

.Fields.Append .CreateField("CT_ID", dbLong)
.Fields.Append .CreateField(strTmpIDFld, dbLong)
.Fields.Append .CreateField(strTmpDateFld, dbDate)
.Fields.Append .CreateField(strTmpDataFld, dbDouble)

End With
.
.
.
dtAdd = AdjustTime(dtStart)
Do While dtAdd < dtEnd + 1 + 15 / 1440
rstDataTemp.AddNew
rstDataTemp.Fields(strTmpDateFld).Value = dtAdd
rstDataTemp.Update
dtAdd = dtAdd + 15 / 1440
Loop

I then use the FindFirst method to search for a record with a specific
date/time. Here is that line in the code:

rstDataTemp.FindFirst ("[" & strTmpDateFld & "]" & "=#" + strAdjDate +
"#")

The problem is, it only works for times up to 4/18/2005 15:30 (this
happens to be the first 3 records, but I have tried deleting those
first 3 records and then it finds nothing). I have stopped the code,
copied and pasted the value of strAdjDate into a query, and it doesn't
find the record. I have even copied and pasted a value directly from
the table I created into the criteria of the query builder and it
*still* doesn't return the record.

I have some similar code that creates records with date/time (but one
at a time, it reads the dates from another table then writes to this
table), and I use a FindFirst on that and it works fine:

rstData.FindFirst ("[" & strDateFld & "]" & "=#" + theAdjDate + "# AND
[" & strIDFld & "] = " & Trim(Str(numID)))

So I'm thinking it has to do wtih the way I'm creating the table in the
first place?? Anyone have any idea?

Thanks
Rebecca

[rivka.howley]

Allen,
That worked better than what I was doing. However, it did not work for
every 3rd record. I'm not sure why, but probably with some fiddling I
could have gotten it to work.

But what I did instead is I added records with dates as I needed to -
ie I read from my import table, and if the date did not exist in the
table I was creating, I added a record. I should have done this from
the beginning, but I thought it would be easier to work with a table
where the records were already populated with dates.

Thanks for your help.