Capturing data between AM and PM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to write an expression in a query that captures the data between
11:01PM and 7:00AM. Here is what I have thus far:

THIRDANDOFFSHIFT: Sum(IIf([ISSUETIME] Between #11:01:00 PM# And #7:00:00
AM#,[EXTENDEDCOST],0))

This is unfortunately not working. I thought maybe if I wrote it as

Between (11:01:00PM And 11:59:00PM) And 12:00:00AM And 7:00:00AM. I am not
sure how to write this statement, though. Any help is greatly appreciated.
Thanks.
 
Mr. Smiley said:
I am trying to write an expression in a query that captures the data between
11:01PM and 7:00AM. Here is what I have thus far:
Click to expand...
[snip]

Check out the dates functions. You need to add 8 hours to the first
date.
It's on the next day. If you don't, then 11PM is always later than 7AM.
Socks
 
The construction of the test would be:

(between 11:01pm and 11:59pm) OR (between 12:00 am and 7:00am)
{no time will ever be selected if you use AND
instead of the OR.}

but that is beside the point.

If you are trying to select anything betwen 11pm on one day and 7:00am
the next day they you MUST add the aspect of date into the testing
structure.

For instance the OR test that I quoted above, if you do not add the
aspect of date into it will get you everything between 11: pm and
midnight
and everything betwen midnight and 7am of the same day
{everything from yesterday morning and everything from yesterday
evening but nothing from this morning.

Ron
 
I am trying to write an expression in a query that captures the data between
11:01PM and 7:00AM. Here is what I have thus far:

THIRDANDOFFSHIFT: Sum(IIf([ISSUETIME] Between #11:01:00 PM# And #7:00:00
AM#,[EXTENDEDCOST],0))

This is unfortunately not working. I thought maybe if I wrote it as

Between (11:01:00PM And 11:59:00PM) And 12:00:00AM And 7:00:00AM. I am not
sure how to write this statement, though. Any help is greatly appreciated.
Thanks.
Click to expand...

Date/Time values are stored as numbers, a count of days and fractions
of a day (times) since midnight, December 30, 1899. Hence, especially
if (as in your case) you're dealing with spans of time across
midnight, it's really better to store the date and time in the same
field.

If you have just a pure-time value, it corresponds to a time on
December 30, 1899. To get all times on the nightshift, you would need
to find all times between 11pm and midnight, and then - separately -
all times between midnight and 7am. You might try

IIF([Issuetime] > #11:00pm# OR [Issuetime] <= #07:00am#)

If you store the date in ISSUETIME as well, you can easily get the
time between 11pm January 11 and 7am January 12. Clearly this will
require redesigning some of your other forms and queries though!

John W. Vinson[MVP]
 
Back
Top