Criteria, Between two times...

  • Thread starter Thread starter Sid
  • Start date Start date
S

Sid

I'm trying to select all records(between two times) that have a stored
[Time_On] like (04/20/2004 3:45 PM).

To select these I'm trying to use two stored reference times like
[Shift1Begin] and [Shift1End], both stored like 06:00 AM, 02:30 PM
respectively. I have tried everything in tests like:
Between(Format([Time_On], hh:nn AM/PM) =>"06:00 AM" AND =< "02:30 PM"
But my syntax is never correct. Should I change to 24-Hour format?
This will eventually be an SQL.
Any suggestions appreciated.
Sid
 
I'm trying to select all records(between two times) that have a stored
[Time_On] like (04/20/2004 3:45 PM).

To select these I'm trying to use two stored reference times like
[Shift1Begin] and [Shift1End], both stored like 06:00 AM, 02:30 PM
respectively. I have tried everything in tests like:
Between(Format([Time_On], hh:nn AM/PM) =>"06:00 AM" AND =< "02:30 PM"
But my syntax is never correct. Should I change to 24-Hour format?
This will eventually be an SQL.
Any suggestions appreciated.
Sid
Click to expand...

Are ShiftBegin and ShiftEnd *just* times, with no corresponding date?

Note that a Date/Time value is NOT A STRING - it's stored internally
as a Double Float number, a count of days and fractions of a day
(times) since midnight, December 30, 1899. So 06:00 AM is actually
stored as 0.25, and corresponds to #12/30/1899 06:00:00# in full form
- and your workers probably were not on site back in the 19th century!

I'd suggest a criterion on TimeOn of

BETWEEN DateValue([TimeOn]) + [Shift1Begin] AND DateValue([TimeOn]) +
[Shift1End]

to add the date to the shift start/end. HOWEVER... this will NOT work
as desired for any shift that spans midnight, since the start time and
end time are on different days!

Note that # is the appropriate delimiter for Date/Time fields, not " -
one possibility would be a calculated field:

TimeOnly: TimeValue([TimeOn])

with criteria of

BETWEEN #06:00 AM# AND #02:30 PM#

or, for the midnight shift,

BETWEEN #10:00 PM# AND #12:00 PM# OR BETWEEN #12:00 AM# AND #06:00 AM#
 
John
ShiftBegin & ShiftEnd are just times like I mentioned below, stored like
00:06:00 AM.
I realize the midnight problem. Wow, strange that you would mention 1899,
something happened to 20 or 30 lines of my sample data that changed my
LogDate(short dates, mm:dd:yyyy) to 12/31/1899 I think.... At this point I
can change my data format. I worked with one Access 2.0 database that used
a 5-Digit number for a date field (=mm:dd:yyyy).
Sid

John Vinson said:
I'm trying to select all records(between two times) that have a stored
[Time_On] like (04/20/2004 3:45 PM).

To select these I'm trying to use two stored reference times like
[Shift1Begin] and [Shift1End], both stored like 06:00 AM, 02:30 PM
respectively. I have tried everything in tests like:
Between(Format([Time_On], hh:nn AM/PM) =>"06:00 AM" AND =< "02:30 PM"
But my syntax is never correct. Should I change to 24-Hour format?
This will eventually be an SQL.
Any suggestions appreciated.
Sid
Click to expand...

Are ShiftBegin and ShiftEnd *just* times, with no corresponding date?

Note that a Date/Time value is NOT A STRING - it's stored internally
as a Double Float number, a count of days and fractions of a day
(times) since midnight, December 30, 1899. So 06:00 AM is actually
stored as 0.25, and corresponds to #12/30/1899 06:00:00# in full form
- and your workers probably were not on site back in the 19th century!

I'd suggest a criterion on TimeOn of

BETWEEN DateValue([TimeOn]) + [Shift1Begin] AND DateValue([TimeOn]) +
[Shift1End]

to add the date to the shift start/end. HOWEVER... this will NOT work
as desired for any shift that spans midnight, since the start time and
end time are on different days!

Note that # is the appropriate delimiter for Date/Time fields, not " -
one possibility would be a calculated field:

TimeOnly: TimeValue([TimeOn])

with criteria of

BETWEEN #06:00 AM# AND #02:30 PM#

or, for the midnight shift,

BETWEEN #10:00 PM# AND #12:00 PM# OR BETWEEN #12:00 AM# AND #06:00 AM#
Click to expand...
 
Back
Top