I was wondering if someone could explain why I have to make this rather brash solution within my otherwise simple select query.

I am querying a commercially written db based in Access and I am sure they have done it for very clever reasons, please look at attached image #1 and #2.
The source table, which I can query but not amend (not that I would try - it was an expensive application) has the COMPLETIONTIME field prefixed with the date 01/01/1900. There is no getting away from it.
I have to include this date in the criteria otherwise the query only works for the COMPLETIONDATE field.

What I am trying to do here is pull all records after 6am the previous day. I will have the user select the date but I want the time to stay at 6am regardless.
Why have the experts who wrote this db made the COMPLETIONTIME always have this peculiar date at the front?

The Access Date/Time datatype is used for, well, recording a date/time value (a point in time). If the originator of that application "typed" the field as a date/time type, it will have a date/time value (i.e., a point in time).

If you use a date/time field and only give it a date value (e.g., using the Date() function), Access will still store a time value -- in this situation, a time of 00:00:00 AM - midnight.

Are you trying to do "duration" calculation (amount of time between two points in time), or are you trying to determine a point in time value? If the former, good luck! That isn't how Access date/time fields are defined.

More info, please...

Probably because they didn't know what they were doing in Access and maybe used other programs, like Excel, in the past.

Debug.Print CDbl(#01/01/1900#) = 2

Debug.Print CDbl(#01/01/1900 06:00#) = 2.25

Notice that first day of the last century is 2 in Access. 12/30/1899 is actually day 0 and don't forget that there is a 12/31/1899.

If they were trying to get to 0 or 0.25, they failed by a couple of days.

Some programs use things like 1904 to get around a strange Leap Year problem in 1900 where it is NOT a leap year.

Some interesting, if not old, reading on the subject:
http://www.calendarhome.com/converter/excel.html

The writers of the afore-mentioned database chose to split the date and the time components out.

Without going into a lot of detail, Access stores datetime values as numbers. The integer part of the number is the date and the decimal part of the number is the time.

You're seeing the integer component of the time field displaying as 1/1/1900.

What you're not seeing is the decimal part of the date, which defaults to 12:00:00 AM. ... but it's there.

I would simply add the date and the time together and query off of the combined value. That way you don't need two separate pieces of criteria.

That is a great insight, thank you guys.

To expand the explanation, the field, WO is the primary key and is used throughout the rest of the db as a foreign key. Perfecting total control over the completion time fields will allow me to write some very useful reports the application designers could not have anticipated. I am not trying to do any calculations with the date or time, just trying to isolate points in time to report from or to.

So, is it reliable enough just to keep putting the 01/01/1900 prefix in front?
I am not going to get any silly surprises further down the line?

Tom

Please note -- you are NOT "putting a prefix of 1/1/1901 in front". If you don't tell Access the date, only a fractional decimal, you're telling Access to use the first date it knows (that 12/31/1899 date).

If you want to know the date portion of a value, you can use a function to strip out that piece.

If you want to know the time portion of a value, you can use a function to strip out that piece.

I'm still under the impression that it is the time portion you wish to use. If so, why? What will having, say, 6AM allow you to do?

You could get the time portion of the time field by using the VBA timeValue function.

The cost of doing so would be slower performance when filtering the records.

Field: JustTheTime: IIF([CompletionTime] is Null,Null,TimeValue([CompletionTime]))
Criteria: >= #06:00:00#

Of course, if you know that there is always a value in CompletionTime, you can just use
Field: TimeValue([CompletionTime])
Criteria: >= #06:00:00#

The records are for a daily report.
Each employee in the dept fills a record in to say what they have done from their shift.
Each new record gets a new WO number.
Our daily report reads from 6am the previous day so they can sit and discuss what happened over the last 'day' and examine what can be done.
That's where the 6am time comes in.
Currently - I have a datasheet form in operation at the moment. Whoever prints the reports dials back through the records from the most recent, chooses the 'cut-off' manually and this WO value is used to report from.
I would be nice to be able to do it automatically at the press of a button that's all.
ie, yesterday is only today minus 1 and 6am is always 6am..

If your "day" starts at 6AM, do you need to store that? That is, if it is a business rule, just the way you do things, wouldn't having only yesterday's date tell you the same thing?

Take a look at Access HELP for the Date() and the Now() functions, then check to see which (if either) is being used in your application to fill that field.