Difficulty Searching TIME in Access Database

[statmanron]

I created a lookup table in Microsoft Access that lists long times between 9:31:00 AM to 4:00:00 PM. Through the query tool, I can successfully find the record times between 9:31:00 AM and 9:33:00 AM... but nothing greater than those three values.

The query works for 9:33:00 AM and below but doesn't work anything greater than 9:34:00 PM

SELECT [Session Time Frames].SessionTime, [Session Time Frames].Min1SessionTime
FROM [Session Time Frames]
WHERE ((([Session Time Frames].Min1SessionTime)=#9:33:00 AM#));

SELECT [Session Time Frames].SessionTime, [Session Time Frames].Min1SessionTime
FROM [Session Time Frames]
WHERE ((([Session Time Frames].Min1SessionTime)=#9:34:00 AM#));

Is there a known bug in Microsoft Access????

 

[SkipVought]

hi,

Time VALUES in Access are NUMBERS. 0.25 is 6:00 AM, 0.5 is 12:00 PM. 0.75 is 6:00 PM These are call floating point values, and as such, there can be a lack of precision when doing an equality as you have posted.

9:34:00 is 0.398611111111111

Do you see the repeating values? So if the STORED value is 0.398611, it will still display the same Time, however the actual VALUE would make your equality FALSE!!!

So you must decide what RANGE of time values might be acceptable in your query, like...

WHERE ((([Session Time Frames].Min1SessionTime) >= #9:34:00 AM# And [Session Time Frames].Min1SessionTime) < #9:34:01 AM#));

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[statmanron]

Skip...You're a genius. To make it easier for myself, I converted the dates into a text field because the data is not going to change in the lookup table. Thanks for your help!!!!