Link to home
Start Free TrialLog in
Avatar of flemingg62
flemingg62

asked on

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
Avatar of ste5an
ste5an
Flag of Germany image

You're mixing data type with visualization.

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#.

User generated image
The other trick is to filter for a formatted value, thus converting it: Format([Time], "hh:nn:ss") = "11:00:00".

User generated image
can you describe the problem a little more?
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of flemingg62
flemingg62

ASKER

Tks that explains it, any idea how I might link it to another table where the time is Exactly 11:00:00
Thanks I'll try the TRIM
Trim is for strings. I wouldn't recommend using trim when the value is stored as datetime.
As everyone has already mentioned, time values in datetime fields may not be exactly equal to what's displayed.
Without fixing your datetime field to round off the values to exactly the datetime you want, you can get unseen differences.
I added an autonumber field so I could see what records had different time values.
User generated imageOnce you've rounded off your time values to the exact value you want to display, you get this:
User generated image
I "fixed" (updated) the Time column with:
TimeValue(CStr([Time]))

Open in new window

If you don't want to "fix" the values in your table, another option, which may be more troublesome, is to use the formula above in a query to convert the Time column values to rounded values.... but you probably already knew that...
When you want to "fix" your values, either using Trim([Time]) or TimeValue(CStr([Time])), keep in mind, those methods use formatting's internally. While it's unlikely that these change in the near future, it means relying on something which can change and is out of your control.

Thus I prefer

TimeSerial(Hour([Time]);Minute([Time]);Second([Time]))

Open in new window

to specify exactly what to convert.
"While it's unlikely that these change in the near future, it means relying on something which can change and is out of your control."
Think of the odds.....
Thanks all
Strange case nonetheless
I think there may have been some records/values coming in from Excel, which is notorious for adding very, very small changes to values.  I've seen it a lot where data types that accept very, very small values are not exactly the same as the data displayed.

Unless you try doing something like this with them, you don't notice.