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

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


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

can you describe the problem a little more?

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.
Once you've rounded off your time values to the exact value you want to display, you get this:

I "fixed" (updated) the Time column with:

TimeValue(CStr([Time]))

Select allOpen 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]))

Select allOpen 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.