We help IT Professionals succeed at work.

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

flemingg62
flemingg62 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

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

Capture.PNG
can you describe the problem a little more?
Software & Systems Engineer
Commented:
If you perform a Trim UPDATE it will get resolved
UPDATE [aaaa] SET [aaaa].[Time] = Trim([Time]);

Open in new window

Author

Commented:
Tks that explains it, any idea how I might link it to another table where the time is Exactly 11:00:00

Author

Commented:
Thanks I'll try the TRIM
Trim is for strings. I wouldn't recommend using trim when the value is stored as datetime.
Mark EdwardsChief Technology Officer

Commented:
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.
Timediff-Unfixed.pngOnce you've rounded off your time values to the exact value you want to display, you get this:
Timediff-Fixed.png
Mark EdwardsChief Technology Officer

Commented:
I "fixed" (updated) the Time column with:
TimeValue(CStr([Time]))

Open in new window

Mark EdwardsChief Technology Officer

Commented:
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...
ste5anSenior Developer

Commented:
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.
Mark EdwardsChief Technology Officer

Commented:
"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.....

Author

Commented:
Thanks all
John TsioumprisSoftware & Systems Engineer

Commented:
Strange case nonetheless
Mark EdwardsChief Technology Officer

Commented:
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.