Access query date/time returns inappropriate records

0
votes

I have an interesting problem that I have been unable to find an answer for! I am writing queries to look at data based on the times of two different events, the difference in the arrival of two units to a location. This is fairly straightforward.

data and part two See below for 'part two' clarification.

[Elapsed_Time] was calculated as the difference between the two and is a date/time.

The first query used is to look at all occurrences where the difference is less than 30 seconds. 

qry = "INSERT INTO [table] SELECT * FROM [New Data] WHERE ([1st_Agency_Onscene] = 'ABCD') AND ([Elapsed_Time] < #12:00:30 AM#);"
DoCmd.RunSQL qry

When the query is run it should return 279 rows but instead returns 281, two extra. Looking at the two extra rows the following is returned:

Refer now to 'part two' in the previous image (I may only have two links!).

They are both at 30 seconds so they should not be returned. There are other records that also have a time of 30 seconds that are not returned, however (6 others, 8 total).

I am not sure how/why they are stored differently than the others that are also 30 seconds. In an attempt to find the difference I created a field that subtracted the [2nd_Agency_Onscene_Time] from the first and it appears they are somehow stored differently. See the two highlighted records.

perhaps this is significant

What is making the data be returned though it clearly should not be? In addition every date range has a few similar occurrences.

I appreciate your thoughts and assistance.

2
ms-accessvba
What happens if you make the elapsed time an integer in seconds and use parameter <30? – June7
You say "[Elapsed_Time] was calculated as the difference", but what's the precise formula you used to get the difference? – C Perkins
@C Perkins That is a good thought. I was given the table of data to work with, I will follow up and see how it was specifically calculated. – dw013

2 Answers

2
votes

This happens because you are comparing doubles which is the underlying data type of Date.

So, use for example the native functions to replace ([Elapsed_Time] < #12:00:30 AM#)

(DateDiff('s', [Elapsed_Time], #12:00:30 AM#) > 0)
1
votes

It's because of the way Access stores Date/Time. It is stored as a double where the number 1 represents one day and the decimals represents hours, minutes, and seconds. Your calculations are returning something slightly less than 30 seconds i.e .00034722221607808 is less than .000347222... but both show as 12:00:30.

I would combat this by altering your calculation to turn the time into an integer and finding the difference between the integers before you find the elapsed time.

FYI Gustav's Answer is better than mine.