Time period converted into number

[kvest]

I have a time field (IncTime) that I would like to use to create another field (Shift) based on a specified time period. The update would only need to be done once since the IncTime value will not change once entered. For example,
12:01-7:00 am = 1
7:01 - 4:00 pm = 2
4:01 pm - 12:00pm = 3
blank = null

I assume that I need an Update Query with a IIF statement, but am confused on how to show a range of time as shown above to get one value.

The final field (Shift) will be used to compare activity between the shifts on a Pivat Table.

Thanks!!!

 

[alvechurchdata]

A Switch statement might be better than iif. Something like:

Code=Switch(IncTime="12:01-7:00 am", 1, _
            IncTime="7:01 - 4:00 pm", 2 _
            IncTime="4:01 pm - 12:00pm", 3)

Having read your post again. are you looking to detect whch shift a particular time fits in? If so you might have to use TimeValue each time:

IncTime>=TimeValue("12:01") and IncTime<=TimeValue("07:00")

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[kvest]

alvechurchdata,
Yes, I am trying to automate the process of determining what shift the incident fits into and generate a number value for that. I will still like to maintain the IncTime value for use in Pivotcharts.

Which way should I go? Or do you need more info?

Thanks...

 

[lespaul]

you don't need to store this shift number, you can just calculate it in the query every time you need it.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[kvest]

Sorry if this is very basic, but I am very new at this and unless it is in the table so I can see it and use it for other purposes, I am a duck out of water. My tables are pretty small anyway, so I am not worried about space.

I will check into both options and see what I can figure out.

Thanks!!!