Help with time

[Chuck F via AccessMonster.com]

Hi There! Any MVP's that would like to help out with this "iif" clause I'd
much appreciate it. I have a bunch of fields within my table one of which
denotes the time. I want to take a certain range of time and assign it to a
specific daypart such as EM, DA, EF, PR, LN, or ON. Below is what I am
currently doing. It works for the most part but for some reason it's not
working for the times from 9:00 AM-1:59 PM.

daypart: IIf(TimeValue([time]) Between "6:00 AM" And "9:59 AM","EM",IIf
(TimeValue([time]) Between "10:00 AM" And "2:59 PM","DA",IIf(TimeValue([time])
Between "3:00 PM" And "6:59 PM","EF",IIf(TimeValue([time]) Between "7:00 PM"
And "10:59 PM","PR",IIf(TimeValue([time]) Between "11:00 PM" And "1:59 AM",
"LN",IIf(TimeValue([time]) Between "2:00 AM" And "5:59 AM","ON","ERROR"))))))

Any suggestions would be much appreciated.

Thanks,

Chuck

 

[John Vinson]

Hi There! Any MVP's that would like to help out with this "iif" clause I'd
much appreciate it. I have a bunch of fields within my table one of which
denotes the time. I want to take a certain range of time and assign it to a
specific daypart such as EM, DA, EF, PR, LN, or ON. Below is what I am
currently doing. It works for the most part but for some reason it's not
working for the times from 9:00 AM-1:59 PM.

daypart: IIf(TimeValue([time]) Between "6:00 AM" And "9:59 AM","EM",IIf
(TimeValue([time]) Between "10:00 AM" And "2:59 PM","DA",IIf(TimeValue([time])
Between "3:00 PM" And "6:59 PM","EF",IIf(TimeValue([time]) Between "7:00 PM"
And "10:59 PM","PR",IIf(TimeValue([time]) Between "11:00 PM" And "1:59 AM",
"LN",IIf(TimeValue([time]) Between "2:00 AM" And "5:59 AM","ON","ERROR"))))))

Any suggestions would be much appreciated.

First off- be aware that Time values are NOT STRINGS, and comparing
them with strings will not work as you wish.

Secondly, deeply nested IIF's are really inefficient. The Switch()
function is better in this case, in that it takes any number of pairs
of arguments; it goes through the pairs left to right, and returns the
second member of the first pair for which the first member is True. So
you should be able to use:

Switch(
TimeValue([time]) >= #06:00# AND TimeValue([time]) < #10:00#, "EM",
TimeValue([time]) >= #10:00# AND TimeValue([time]) < #15:00#, "DA",
<etc. etc.>
True, "ERROR")

Thirdly - you may want to create a small Table with fields StartTime,
EndTime, and DayPart with these values. A "Non Equi Join" query will
let you look up the time in the table - which can be edited much more
easily than a complicated IIF or SWITCH function call:

SELECT <whatever fields>, DayParts.DayPart
FROM yourtable INNER JOIN Dayparts
ON TimeValue([yourtable].[time]) >= DayParts.StartTime
AND TimeValue([yourtable].[time]) <= DayParts.EndTime
WHERE <other criteria>

If [Time] is in fact a date/time field containing only a pure time
(that is, it's internally stored as a number between 0 and 1,
corresponding to a time on December 30, 1899) then you don't need the
TimeValue() function. If you use the fieldname time be sure to always
use square brackets - it's a reserved word, and best avoided.

John W. Vinson[MVP]

 

[Chuck F via AccessMonster.com]

Hi There! Any MVP's that would like to help out with this "iif" clause I'd
much appreciate it. I have a bunch of fields within my table one of which

[quoted text clipped - 10 lines]

Any suggestions would be much appreciated.

First off- be aware that Time values are NOT STRINGS, and comparing
them with strings will not work as you wish.

Secondly, deeply nested IIF's are really inefficient. The Switch()
function is better in this case, in that it takes any number of pairs
of arguments; it goes through the pairs left to right, and returns the
second member of the first pair for which the first member is True. So
you should be able to use:

Switch(
TimeValue([time]) >= #06:00# AND TimeValue([time]) < #10:00#, "EM",
TimeValue([time]) >= #10:00# AND TimeValue([time]) < #15:00#, "DA",
<etc. etc.>
True, "ERROR")

Thirdly - you may want to create a small Table with fields StartTime,
EndTime, and DayPart with these values. A "Non Equi Join" query will
let you look up the time in the table - which can be edited much more
easily than a complicated IIF or SWITCH function call:

SELECT <whatever fields>, DayParts.DayPart
FROM yourtable INNER JOIN Dayparts
ON TimeValue([yourtable].[time]) >= DayParts.StartTime
AND TimeValue([yourtable].[time]) <= DayParts.EndTime
WHERE <other criteria>

If [Time] is in fact a date/time field containing only a pure time
(that is, it's internally stored as a number between 0 and 1,
corresponding to a time on December 30, 1899) then you don't need the
TimeValue() function. If you use the fieldname time be sure to always
use square brackets - it's a reserved word, and best avoided.

John W. Vinson[MVP]

John,

First of all, you are amazing and I really appreciate giving me some of your
valuable time to answer my questions. You have a lot of helpful suggestions.
I tried the Switch function and it seemed to work for all but the time
between 11:00 PM and 2:00 AM. This is what I have.

daypart: Switch([mtime]>=#11:00:00 PM# And [mtime]<#2:00:00 AM#,"LN",[mtime]

=#2:00:00 AM# And [mtime]<#6:00:00 AM#,"ON",[mtime]>=#6:00:00 AM# And [mtime]

<#10:00:00 AM#,"EM",[mtime]>=#10:00:00 AM# And [mtime]<#3:00:00 PM#,"DA",
[mtime]>=#3:00:00 PM# And [mtime]<#7:00:00 PM#,"EF",[mtime]>=#7:00:00 PM# And
[mtime]<#11:00:00 PM#,"PR",True,"ERROR")

The time in mtime is an actual date/time format so I omitted the TimeValue
that I was using before. The time field was originally imported as text in
military time and I used mtime: CDate(Format([milltime],"@@\@")). Then
from that table I used the above Switch funciton to change the ranged values
to a specific daypart. I still cannot get the times between 11:00 PM and 2:
00 AM to work as it shows as ERROR from the above function.

Any further suggestions? I'd like to stick with the switch function and not
have to create a seperate table with start and end times like you suggested.
But if this won't work then I will surely use it.

Thanks,

Chuck

 

[John Vinson]

First of all, you are amazing and I really appreciate giving me some of your
valuable time to answer my questions. You have a lot of helpful suggestions.
I tried the Switch function and it seemed to work for all but the time
between 11:00 PM and 2:00 AM. This is what I have.

daypart: Switch([mtime]>=#11:00:00 PM# And [mtime]<#2:00:00 AM#,

Change this AND to OR and you should be in good shape. All pure Time
values are on the same day - December 30, 1899; and there was no time
on that day which was simultaneously after 11pm and before 2am. What
you need is the two blocks of time - either after 11pm, *or* before
2am.

John W. Vinson[MVP]

 

[Chuck F via AccessMonster.com]

First of all, you are amazing and I really appreciate giving me some of your
valuable time to answer my questions. You have a lot of helpful suggestions.
I tried the Switch function and it seemed to work for all but the time
between 11:00 PM and 2:00 AM. This is what I have.

daypart: Switch([mtime]>=#11:00:00 PM# And [mtime]<#2:00:00 AM#,

Change this AND to OR and you should be in good shape. All pure Time
values are on the same day - December 30, 1899; and there was no time
on that day which was simultaneously after 11pm and before 2am. What
you need is the two blocks of time - either after 11pm, *or* before
2am.

John W. Vinson[MVP]

Worked beautifully thx.