I have an access data base where some time are not correctly formatted as time. i.e. 11:00:00 is good but 11:00:00 is bad
Hi. Database5.accdbI have an access data base where some time are not correctly formatted as time. i.e. 11:00:00 is good but 11:00:00 is bad (They are in the same field formatted as time)
I have attached a Access DB with one table and 102 records. The issued is with the column called time. If you right click on time and filter for 11:00:00 you either get 75 or 27 records returned. I need to connect to other tables with date and time but the 27 records wont match. Any ideas why some 11:00:00 are not been recognised as 11:00:00
The column [Time] is of the data type Date/Time. Thus what you see is the value applied with a certain format. This means, that there is sometimes data no displayed.
Furthermore, Date/Time is internally a stored as double. Thus floating point arithmetic's apply. The trouble is #11:00:00.000# is not an exact decimal fraction (0,458333333333333..).
Thus when filtering using the correct domain for time you need to filter for a range, not a point in time. This means, when you want to filter for the point #11:00:00# then you need to filter instead on the range >= #11:00:00# AND < #11:00:01#.
The other trick is to filter for a formatted value, thus converting it: Format([Time], "hh:nn:ss") = "11:00:00".