Date and time criteria

[alcifer]

I give up. Please help.
I need to perform a query that will give me records for a range of dates and for the same hours (like a work shift) each day. My table has both a time and date field. I have been successful at querying the records for the given dates, but when I try to limit by time of day (say 8 am to 4 pm) I either get no records or from 8 am of the first date to 4 pm on the last date.

I will be using this data to feed a report.
From reading other posts, I will mention that the date is saved in the table as short date format and using Date() time is short time being fed by the Now() command.

 

[skipit]

Hello alcifer,

I do something similar. I have three queries accessing a table of test results. The table has a RecordDate field (setup as Short date) and a RecordTime field (setup as Short time). I have a query for the Midnight Shift, Afternoon Shift and the Day Shift. In all three queries I take the past thirty day’s results using the following criteria in the query build window under the RecordDate column.

Criteria for RecordDate this is the same in all three queries:

Between (Date()-30) And Date()

The RecordTime is a little different for each query depending on the shift's working hours. See below…

Criteria for RecordTime:

The criteria below is for Midnight shift which is a time frame of 11:30pm to 7:19:59am of the next day this criteria is for the RecordTime Field in the Midnight Shift query.

Between #11:30:00 PM# And #11:59:00 PM# Or Between #12:00:00 AM# And #7:19:59 AM#

The criteria below is for Day shift which is a time frame of 7:20am to 3:30pm of the same day this criteria is for the RecordTime Field in the Day Shift query.

Between #7:20:00 AM# And #3:30:00 PM#

The criteria below is for Afternoon shift which is a time frame of 3:31pm to 11:29:59pm of the same day this criteria is for the RecordTime Field in the Afternoon Shift query.

Between #3:31:00 PM# And #11:29:59 PM#

This can be adjusted to your timeframes. I have not had any trouble with this method as of yet. There is another way to do this using a module that I had tried but I think the query method works better for what I am doing. Hope this helps.

Thanks Skip

 

[alcifer]

Thanks for your detailed reply. Unfortunately it doesn't work for me. I tried to cut and paste the query parameters:
Between #7:20:00 AM# And #3:30:00 PM#
and I had no results (should be plenty).
I changed the query parameters to
>#7:00:00 AM# just to try and limit the records but this time all of the records appeared, even those before 7 am.

As you can see I am having a real hard time with this. I can easily get records based on date criteria, but as soon as I try to limit by time, it's like a whole new animal (which seems to be a 500 lb gorilla for me!!).

If anyone else has some strategies to deal with time, I'm all ears.
Thanks, Al

 

[alcifer]

Ok,
I'm partway there...at least in understanding. I created a dummy database that uses time() instead of the now() function to autofill. In doing so, I can limit the records to the time I want. That being said, i already have over 17,000 records created with the Now() function and would kind of like to keep things this way. After doing some research, I find that the now() function is stored in access as a decimal value, such as 13123.23423 where the left side of the decimal is an expression of the date, and the right side the time(expressed as a decimal of 24). Time() only stores the right side of the decimal.
The question then becomes, how can I get access to look only at the time when I specify criteria and not the date (I have a seperate field for the date).
It seems that this question, or a flavour thereof, has come up quite often. It would be great to have some definitive answers on this if possible.
Many thanks in advance, Al

 

[Clepto]

I have a similar problem. I am using Now() to capture a unique date/Time, to differentiate between an order and the update of that order. I want to keep the old order lines as an audit trail.
However when I use the date/time as a query criteria I sometimes get no records. I say sometimes because the results are inconsistent. It works on some records and not others.

I thought this might be a problem with the date/time format, so I changed to using the underlying number as a double with auto decimal places, but no change.

I have even created a test query with 1 column and pasted the double from the table to the query and got nothing?


Should I just turn it into a string and have done with it?

 

[jdraw]

Clepto,

How about describing your proposed database in plain English. An overview to give people some idea of what this means.

You have attached your post to a thread that is 13 years old. Probably better to start a new thread with info on your requirements.

Put this into context

.....differentiate between an order and the update of that order.

See this for Audit log info

 

[Clepto]

Sorry for the delay in responding. I work nights and sent my post after a frustrating morning.

In short, I want to use date/time as a unique identifier in an order. Access stores date time variables as a double, which is a floating point number and in the interim I have found an item headed: “Floating Point Expressions Do Not Compare As Equal” which accounts for my problem in using them as query criteria, so I plan to convert it to a string. Many thanks for your reply to my post

 

[Minty]

This thread http://www.access-programmers.co.uk/forums/showthread.php?t=274554 has a bit more on this "feature"

 

[jdraw]

Clepto,

I would consider using separate fields. That is, an autonumber for OrderId for DBMS purposes, and a separate field of Date/Time data type for each Date that you need.
1 field, 1 fact and I wouldn't get into the issue of rounding/precision in Date/Time to deal with uniqueness.
Good luck.