Seek Doesn't Work (sometimes)

[Pete B]

Very frustrated. I've seen this before with Access and fixed it but cant'
tell you how!! The problem comes and goes. It has just re-surfaced after
being gone for over two years.

The problem is using "seek" in VBA against a table that contains dates.
1) The index is first set on the date/time field in which I will seek.
2) The seek is then performed using the "=" seek parameter.
3) It doesn't find it.....

Is this a known problem? Is there a fix?

Notes:
1) The searched date/time DOES match an existing date/time in the table.
2) Using ">=" as the seek operator finds the record!! But using this method
doesn't solve my problem in all cases.
3) When I use ">=" in the debug mode, I've added debug code that tests the
value in the found record against the sought value and VBA tells me the two
values are NOT equal (although the really are!!)

 

[Arvin Meyer [MVP]]

Just a thought: If your date was entered with the Now() function, it may
display as a date only, but actually be stored with a time that exceeds 0. A
date only is stored with the time of midnight so all records appear.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Pete B]

Thanks for the thought, but my code actually controls/sets the date and time
both for the value in the table and the sought after date/time (don't use
Now())
--
Thanks
Pete B

Just a thought: If your date was entered with the Now() function, it may
display as a date only, but actually be stored with a time that exceeds 0. A
date only is stored with the time of midnight so all records appear.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access