Assigning shift to Time Intervals using IIF

[Leslie]

I have a database with "On Duty" dates and times combined in one field, for
example "04/20/2009 06:015:00 AM". I am trying to group the time values by
shift. The date isn't important. I couldn't figure out how to do this with
such a large date so I created a table with TimeValue and now I am trying to
compare the times using IIF statements:

1st Shift is between 06:30:00 AM and 02:30:00 PM
2nd shift is between 02:30:00 PM and 10:30:00 PM
3rd shift is between 10:30:00 PM and 06:30:00 AM

I have tried writing lots of ways to write this. Here's the latest:
Shift: IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"02:30:00 PM","1st
Shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"10:30:00 PM","2nd
shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"06:30:00 AM","3rd
Shift")))

What is the best way to do this? Do i need the quotes around teh times?
Should I use "Between" and if so, how?

Thanks,

 

[Hans Up]

I have a database with "On Duty" dates and times combined in one field, for
example "04/20/2009 06:015:00 AM". I am trying to group the time values by
shift. The date isn't important. I couldn't figure out how to do this with
such a large date so I created a table with TimeValue and now I am trying to
compare the times using IIF statements:

1st Shift is between 06:30:00 AM and 02:30:00 PM
2nd shift is between 02:30:00 PM and 10:30:00 PM
3rd shift is between 10:30:00 PM and 06:30:00 AM

I have tried writing lots of ways to write this. Here's the latest:
Shift: IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"02:30:00 PM","1st
Shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"10:30:00 PM","2nd
shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"06:30:00 AM","3rd
Shift")))

I would feed the original Date/Time value to a function which returns
the shift name. (You shouldn't need to first extract the TimeValue
piece and store it in a separate field --- just let the function deal
with the full Date/Time)

This is what my attempt looks like:

Public Function whichShift(ByVal pvarIn As Variant) As Variant
Dim dteTime As Date
Dim varOut As Variant

If IsNull(pvarIn) Then
varOut = Null
Else
dteTime = TimeValue(pvarIn)
Select Case dteTime
Case Is < TimeValue("6:30:00 AM")
varOut = "3rd shift"
Case Is < TimeValue("2:30:00 PM")
varOut = "1st shift"
Case Is < TimeValue("10:30:00 PM")
varOut = "2nd shift"
Case Is <= TimeValue("23:59:59 PM")
varOut = "3rd shift"
Case Else
varOut = Null
End Select
End If
whichShift = varOut
End Function

Try it as:

Shift: whichShift([T_ZZ_2009_Crew_Chart_Data]![Your Date/Time Field])

Actually, if you feed it just a TimeValue (without the date component),
it should still work.

 

[John W. Vinson]

I have a database with "On Duty" dates and times combined in one field, for
example "04/20/2009 06:015:00 AM". I am trying to group the time values by
shift. The date isn't important. I couldn't figure out how to do this with
such a large date so I created a table with TimeValue and now I am trying to
compare the times using IIF statements:

1st Shift is between 06:30:00 AM and 02:30:00 PM
2nd shift is between 02:30:00 PM and 10:30:00 PM
3rd shift is between 10:30:00 PM and 06:30:00 AM

I have tried writing lots of ways to write this. Here's the latest:
Shift: IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"02:30:00 PM","1st
Shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"10:30:00 PM","2nd
shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"06:30:00 AM","3rd
Shift")))

What is the best way to do this? Do i need the quotes around teh times?
Should I use "Between" and if so, how?

Thanks,

Well, you certainly do NOT need another table, in fact it would be a big
hassle to have one!

Instead, I'd use the Switch() function. It takes arguments in pairs, and
evaluates them left to right; when it first encounters a True value in the
first member of a pair it returns the second member and quits. So:

Shift: Switch(TimeValue([On Duty]) < #06:30#, "3rd", TimeValue([On Duty]) <
#14:30#, "1st",TimeValue([On Duty]) < #22:30#, "2nd",True, "3rd")

This notes that as a pure time, the 3rd shift is split into two disjoint
periods. Date/Time values are stored as a count of days and fractions of a day
since midnight December 30, 1899; so the third shift is the block at the end
of that day, from 10:30 to midnight, and also a block at the beginning of the
day, midnight to 6:30.

 

[Leslie]

Hi John,

Thank you very much! I ended up using your formula with just some tiny
modifications and it worked really well. Here's what my formula looked like:

Shift: IIf([On Duty Time] Is Null,Null,Switch(TimeValue([On Duty
Time])<#6:30:00 AM#,"3rd",TimeValue([On Duty Time])<#2:30:00
PM#,"1st",TimeValue([On Duty Time])<#10:30:00 PM#,"2nd",True,"3rd"))

I'm not familiar with the Switch function. Can you answer a couple more
questions for me?
1. What is the purpose of the final "True"?
2. How is the final "3rd" used? Does it mean that if none of the above are
true then by default make it 3rd?

Leslie

I have a database with "On Duty" dates and times combined in one field, for
example "04/20/2009 06:015:00 AM". I am trying to group the time values by
shift. The date isn't important. I couldn't figure out how to do this with
such a large date so I created a table with TimeValue and now I am trying to
compare the times using IIF statements:

1st Shift is between 06:30:00 AM and 02:30:00 PM
2nd shift is between 02:30:00 PM and 10:30:00 PM
3rd shift is between 10:30:00 PM and 06:30:00 AM

I have tried writing lots of ways to write this. Here's the latest:
Shift: IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"02:30:00 PM","1st
Shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"10:30:00 PM","2nd
shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"06:30:00 AM","3rd
Shift")))

What is the best way to do this? Do i need the quotes around the times?
Should I use "Between" and if so, how?

Thanks,

Well, you certainly do NOT need another table, in fact it would be a big
hassle to have one!

Instead, I'd use the Switch() function. It takes arguments in pairs, and
evaluates them left to right; when it first encounters a True value in the
first member of a pair it returns the second member and quits. So:

Shift: Switch(TimeValue([On Duty]) < #06:30#, "3rd", TimeValue([On Duty]) <
#14:30#, "1st",TimeValue([On Duty]) < #22:30#, "2nd",True, "3rd")

This notes that as a pure time, the 3rd shift is split into two disjoint
periods. Date/Time values are stored as a count of days and fractions of a day
since midnight December 30, 1899; so the third shift is the block at the end
of that day, from 10:30 to midnight, and also a block at the beginning of the
day, midnight to 6:30.

 

[Leslie]

Hi Hans,

Thanks for your answer. I was not sure where to put this code. Should I
have copied and pasted it straight into the build of the query? Anyway, I
used John's answer but I would like to know how yours works.

Thanks,
Leslie

I have a database with "On Duty" dates and times combined in one field, for
example "04/20/2009 06:015:00 AM". I am trying to group the time values by
shift. The date isn't important. I couldn't figure out how to do this with
such a large date so I created a table with TimeValue and now I am trying to
compare the times using IIF statements:

1st Shift is between 06:30:00 AM and 02:30:00 PM
2nd shift is between 02:30:00 PM and 10:30:00 PM
3rd shift is between 10:30:00 PM and 06:30:00 AM

I have tried writing lots of ways to write this. Here's the latest:
Shift: IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"02:30:00 PM","1st
Shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"10:30:00 PM","2nd
shift",IIf([T_ZZ_2009_Crew_Chart_Data]![On Duty Time]<"06:30:00 AM","3rd
Shift")))

I would feed the original Date/Time value to a function which returns
the shift name. (You shouldn't need to first extract the TimeValue
piece and store it in a separate field --- just let the function deal
with the full Date/Time)

This is what my attempt looks like:

Public Function whichShift(ByVal pvarIn As Variant) As Variant
Dim dteTime As Date
Dim varOut As Variant

If IsNull(pvarIn) Then
varOut = Null
Else
dteTime = TimeValue(pvarIn)
Select Case dteTime
Case Is < TimeValue("6:30:00 AM")
varOut = "3rd shift"
Case Is < TimeValue("2:30:00 PM")
varOut = "1st shift"
Case Is < TimeValue("10:30:00 PM")
varOut = "2nd shift"
Case Is <= TimeValue("23:59:59 PM")
varOut = "3rd shift"
Case Else
varOut = Null
End Select
End If
whichShift = varOut
End Function

Try it as:

Shift: whichShift([T_ZZ_2009_Crew_Chart_Data]![Your Date/Time Field])

Actually, if you feed it just a TimeValue (without the date component),
it should still work.

 

[Hans Up]

Hi Hans,

Thanks for your answer. I was not sure where to put this code. Should I
have copied and pasted it straight into the build of the query? Anyway, I
used John's answer but I would like to know how yours works.

Hi Leslie,

I liked John' answer, too. Both are similar in that they evaluate a
sequence of conditions until a condition which evaluates as "True" is
reached.

If the TimeValue < 6:30 AM, it's 3rd shift

The next condition is TimeValue < 2:30 PM. At that point, you know the
time must be 6:30 or later, because otherwise the Select Case (or Switch
construct in John's version) would have terminated at the first
condition --- and we wouldn't be evaluating the second condition.
Therefore, in order for the second condition to be "True", we know the
TimeValue must be >= 6:30 AM AND < 2:30 PM. So it must be 1st shift.

The additional Case (or Switch) conditions just build on the same concept.

Does that make sense? I'm afraid that's probably the best I can do at
explaining how it works.

If you want to experiment with that function, you can create a new
standard module and paste the function in there. Then you could use it
in a query (pretend you have a table called MyTable which includes a
Date/Time field called shift_start) like this:

SELECT m.shift_start, whichShift(m.shift_start) AS Shift
FROM MyTable As m;

Have fun,
Hans

 

[John W. Vinson]

Hi John,

Thank you very much! I ended up using your formula with just some tiny
modifications and it worked really well. Here's what my formula looked like:

Shift: IIf([On Duty Time] Is Null,Null,Switch(TimeValue([On Duty
Time])<#6:30:00 AM#,"3rd",TimeValue([On Duty Time])<#2:30:00
PM#,"1st",TimeValue([On Duty Time])<#10:30:00 PM#,"2nd",True,"3rd"))

I'm not familiar with the Switch function.

It's a bit obscure but sometimes very handy. See the VBA Help file for a list
of other functions.

Can you answer a couple more
questions for me?
1. What is the purpose of the final "True"?

It returns a value if none of the preceding choices was true.

2. How is the final "3rd" used? Does it mean that if none of the above are
true then by default make it 3rd?

That was a bit of a "hack" shortcut. If none of the other conditions were
true, then the Timevalue must in fact be between 10:30 pm and midnight since
all Timevalue() results must be between midnight at the start of the day and
midnight the next day. There's no need to check for it being greater than
10:30 pm (we've already excluded it being less) and there's no need to check
for it being past midnight (it CAN'T be by the nature of the function).