We help IT Professionals succeed at work.
Get Started

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 asked
on
55 Views
Last Modified: 2019-12-20
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
ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
can you describe the problem a little more?
IT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer

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
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

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

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
CERTIFIED EXPERT

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

Open in new window

Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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 TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2020

Commented:
Strange case nonetheless
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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.
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE