Baffled by Time

[TheSearcher]

My table looks like this. Start_Time and End_Time are both Date/Time fields set to a Long Time format.

The data in my table looks like this.

Therapist	TheDate	Start_Time	End_Time	Client_Name	Client_Id	UID
Jane Smith	10/28/2025	8:30:00 AM	9:30:00 AM	Debbie Jones	600123	560

In Query Design the query looks like this:

But in SQL View it looks like this:

SELECT tbl_Schedule.Client_Id, tbl_Schedule.TheDate, tbl_Schedule.Start_Time, tbl_Schedule.End_Time
FROM tbl_Schedule
WHERE (((tbl_Schedule.Client_Id)='600123') AND ((tbl_Schedule.TheDate)=#10/28/2025#) AND ((tbl_Schedule.Start_Time)<=#12/30/1899 8:30:0#) AND ((tbl_Schedule.End_Time)>#12/30/1899 9:30:0#));

When I include a format function in the query the date part still remains:

SELECT tbl_Schedule.Client_Id, tbl_Schedule.TheDate, tbl_Schedule.Start_Time, tbl_Schedule.End_Time
FROM tbl_Schedule
WHERE (((tbl_Schedule.Client_Id)='600123') AND ((tbl_Schedule.TheDate)=#10/28/2025#) AND ((tbl_Schedule.Start_Time)<=Format(#12/30/1899 8:30:0#,"hh:nn:ss AM/PM")) AND ((tbl_Schedule.End_Time)>Format(#12/30/1899 9:30:0#,"hh:nn:ss AM/PM")));

The query should return one record but it doesn't return anything.
Can someone please explain to me what I'm doing wrong?

ManyThanks,
TS

 

[sonic8]

Am Format just determines the visual display of data but not the actual data stored in the database.
The Date/Time data type always stores Date and Time.

See my post on this topic for further info on how to deal with Date/Time data:

Date/Time in VBA and Access

The complete guide on date and time in your Access and VBA applications.

codekabinett.com

 

[The_Doc_Man]

A time field is capable of holding a fairly precise but rather complex value... a specification for a moment in time. The catch is that humans often tend to separate dates and times of day as though they were unrelated - but they are intimately related.

Your comparison against Start_Time and End_Time, as written the query without the Format function, seeks a time between 8:30 AM and 9:30 AM on 30-Dec-1899 - which is to say, those times on Day 0 of the Access calendar. It looks more or less OK. But the query WITH a Format function is looking at a string that contains punctuation. Date fields are actually typecasts of a DOUBLE field, which means it is subject to floating-point rounding.

You should be OK with the unformatted comparison SQL. Did that work for you?

If you want to REALLY do this, use the rules of string conversion and comparison to avoid the possibility of rounding.

Format( tbl_Schedule.Start_Time, "HH:nn:ss" ) <= "08:30:00"

In an apples to apples comparison, both sides must be held to the same standards. The "HH:nn:ss" is 24-hour format as a string, so the comparator must also have appropriate leading zeros. Once you do that, string-formed times will compare correctly. There are other ways to skin this cat, but this is an easy one.

 

[Isaac]

So Format is a nice way to return data, but doesn't belong in a where clause (unless in some edge case I'm not thinking of now)

 

[RonPaii]

Use the TimeValue and DateValue functions may work.

SELECT tbl_Schedule.Client_Id, tbl_Schedule.TheDate, tbl_Schedule.Start_Time, tbl_Schedule.End_Time
FROM tbl_Schedule
WHERE (((tbl_Schedule.Client_Id)='600123')
AND ((DateValue(tbl_Schedule.TheDate))=#10/28/2025#)
AND ((TimeValue(tbl_Schedule.Start_Time))<=TimeValue(#8:30:0#))
AND ((TimeValue(tbl_Schedule.End_Time))>TimeValue(#9:30:0#)));

 

[TheSearcher]

Thank you. Much needed article!
So, the Start_Time and End_Time displayed in the snapshot of my table above is not an accurate representation of the data stored in that field. That's just wonderful!
Is there anyway to get Access to display what is truly stored in the fields? This would be very helpful.

 

[TheSearcher]

Ron Paii - You are the man!!! Works perfectly! Thank you!!!

 

[The_Doc_Man]

So, the Start_Time and End_Time displayed in the snapshot of my table above is not an accurate representation of the data stored in that field.

Might not be. But the only way to know for sure is to open the given table in Table View. Since you only have a type, not a format, in the field definitions, the displayed table contents SHOULD be in conventional time format. For you purpose of verification of content, you should be able to see it as it appears "in the raw." Note also that while date/time fields are actually type-casts of a DOUBLE field, the data type being date/time will change the default format of the view to something time-related.

Ron's solution, like mine, was to assure that the comparisons are in apples-to-apples format. My choice was string; his was time. The "like compares to like" concept is the most important part of that.

 

[MajP]

Is there anyway to get Access to display what is truly stored in the fields? This would be very helpful.

Really?
So lets say you have this displayed. 10/27/2025 6:36 PM in a field
You would find it very useful to see this 45957.775 instead?
WhatsThere: cdbl([SomeDateField])

 

[DHookom]

Your End_Time value is 9:30 but your criteria is tbl_Schedule.End_Time)>#12/30/1899 9:30:0#)); without the =.