Based on Time of Day

[mikepage5]

I want a value to be added to a field based on the time
of the day. ie, if it is between 5am and 9 am I
want "AM" in the field, if it is between 9:01am and
12:30pm I want "MID" in the field and if it is after
12:31pm I want "PM" in the field, can anyone lead me in
the right direction?

 

[Tim Ferguson]

I want a value to be added to a field based on the time
of the day. ie, if it is between 5am and 9 am I
want "AM" in the field, if it is between 9:01am and
12:30pm I want "MID" in the field and if it is after
12:31pm I want "PM" in the field,

SELECT FORMAT(SomeTime,"hh:mm") &
IIF( TIMEVALUE(SomeTime) <= #05:00#, " PM ",
IIF( TIMEVALUE(SomeTime) <= #09:00#, " AM ",
IIF( TIMEVALUE(SomeTime) <= #12:30#, " MD ", " PM ")))
AS NewSomeTime,
SomeOtherValue
FROM SomeTable


and so on. I don't think your limits are quite right though.

Hope that helps


Tim F