Hi,

I have been wrestling with this for quite some time.

I have products that are made and i need to assign what shift they
were made.

My plant works 3 days on, 4 days off. then 4 days on, 3 days off.
Wednesday is the swing day.
the shifts are 12hrs from 7am - 7pm and 7pm - 7am

Of course at midnight the date changes and i have to deal with that as
well.

I have been going about it by building a "calendar" that has shifts
mapped out like. To deal with the midnight factor I have created
categories (PM1,PM2 and AM).

I was then thinking of doing an "iif" statement and if the time was
between the below times it would assign the proper category. I would
then link the calendar to the table and it could see the date, and
category and then assign the shift...

If time is between
07:00 AM AND 06:59PM ITS IN THE "AM" CATEGORY
07:00 PM AND 12:00AM ITS IN THE "PM1"
12:01AM AND 06:59AM ITS IN THE "PM2"

DATE CATEGORY SHIFT
3/5 PM 1 B
3/5 AM A
3/5 PM2 D
3/6 PM1 D
3/6 AM C
3/6 PM2 D
3/7 PM1 D
3/7 AM C
3/7 PM2 D

ETC ETC

how do i write the iif statement in the query to assign the
category!?!

Thanks, Gavin

something simple like this wont even work

TIMEE: IIf([time] Between #7:00:00 AM# And #6:59:00 PM#,"AM",1)

gavm360 (AT) yahoo (DOT) com wrote:

You could create a function. Drop the following into a module.
Function PassShiftBack(datTime As Date) As String
Dim strTime As String
strTime = Format(datTime, "hh:mm:nn")
Select Case strTime
Case Is <= "06:59:59"
PassShiftBack = "PM2"
Case Is >= "19:00:00"
PassShiftBack = "PM1"
Case Else
PassShiftBack = "AM"
End Select
End Function

I created a table with a field called DatFld. I stuffed that field with
Now() type values: 3/6/2008 10:28:49 AM. I then created a query with
the following SQL statement

SELECT DatFld, PassShiftBack([DatFld]) AS Shift FROM Table1;

This should get you started.

If you had a static starting date (an arbitrary value) and you
subtracted the starting date from the ending date and got the number of
weeks, you could use the Mod operator (see help) to see if it is week 1
or week 2 and based on the day determine if you are on the 4/3 or the
3/4. That's beyond the scope of this message.

Blast
http://www.youtube.com/watch?v=LlmIl70lwW0