Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difficulty Searching TIME in Access Database

Status
Not open for further replies.

statmanron

Programmer
Feb 17, 2012
9
US
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????
 

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...
Code:
WHERE ((([Session Time Frames].Min1SessionTime) >= #9:34:00 AM# And [Session Time Frames].Min1SessionTime) < #9:34:01 AM#));

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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!!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top