Accurately reporting a shift that spans 2 days

[HockeyGuy]

Does anyone have experience with reporting data that doesn't strictly adhere to date grouping? I am trying to report out production by shift where a shift starts on one day and ends on the following day. Specifically, the 3rd shift starts at 10:30 PM, spans midnight and ends at 6:30 AM on the next day.

Currently, when I run the daily production report, that groups by day then by shift, I get the last part of "a" shift 3 from midnight to 6:30 combined with the following shift 3 from 10:30 to midnight. These are two different shift 3's that happen to stop and start on the same day date. How do I get the report to group to a day that is defined as follows:

Shift Starts Ends
Shift1 6:30AM 3:30PM
Shift2 3:30PM 10:30PM
Shift3 10:30PM 6:30AM (spans midnight)


Thanks for any insights.

 

[dhookom]

What I have done is to create a table with a single date/time field and single record that stores the work day start time.

tblWorkDayStart
===============
DayStartTime (#6:30#)

You can then add this to all of your report record sources and subtract the DayStartTime from your "time" fields to calculate the work day.

WorkDate: DateValue([WorkDateTime] - [DayStartTime])

When management re-defines your work days, just edit the value in the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HockeyGuy]

Thanks Duane

So you are saying, that since my first shift starts at 6:30 AM, to subtract #6:30# from all my records at the query level to determine a "workday" then group on WorkDay/Shift.

So 3rd shift production records like 2/19/2006 6:26:09 AM will be evaluated as 2/18/2006, whereas 1st shift records such as 2/19/2006 6:31:09 AM will be calucaled to 2/19/2006.

I like it, I like it a lot. It is always nice when a simple, efficient logic surfaces as a solution.

You are good - thanks again

 

[dhookom]

HockeyGuy,
I was once hired to update a large manufacturing application where the work day started at 7:00 AM. This was hard-coded all over the place in queries and modules. They wanted to change the work day to start at 11:00 PM the previous day. It made sense to use a "data-driven" solution since they would probably change their minds in the future.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HockeyGuy]

Duane:

Just to let you know that I implemented your insights with the following modification. When I subtracted "6:30 AM" from "2/17/2006 12:24:43 PM" I got "38765.2463...". Using the DateValue function gave me the general ERROR# response.

Ultimately I used the following: WorkDate: Format(([DateAndTime]-[DayStartTime]),"mm/dd/yyyy"). While the resultant is a text value, the calculation is right to the day and therefore good for grouping.

I am not sure why DateValue didn't represent the calculated date, perhaps this is a query grid (Jet engine) thing as opposed to VBA.

Thanks again.


Rick

 

[dhookom]

I'm not sure why the calc didn't work. You can use:
datevalue(cdate(38765.2463))
or
datevalue(cdate(#2/17/2006 12:24:43 PM# -#6:30 AM#))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HockeyGuy]

I like DateValue(CDate(#2/17/2006 12:24:43 PM# -#6:30 AM#)). I think that I needed the CDate to convert the resultant "38765.2463" then the DateValue to boil it down to a big day date.

Thanks again

Rick