IIf statement based on Now() function

Question

I am trying to populate a field based on the time I import a file. Please help me with this expression as I keep getting the answer of 9:30am.

IIf(Now()>"05:00 PM"<"09:30 AM","9:30 AM",IIf(Now()>"09:31 AM"<"11:30 AM","11:30 AM",IIf(Now()>"11:31 AM"<"01:00 PM","1:00 PM",IIf(Now()>"01:01 PM"<"03:00 PM","3:00 PM",IIf(Now()>"03:01 PM"<"05:00 PM","4:00 PM")))))

Answer 1

Several things wrong - Your IIF statement syntax is wrong.  IIF(Now() > XXX AND Now() < YYY, TrueAnswer, FalseAnswer)

Access does not have time as "05:00 PM" but as a decimal number where the fraction represent the part of a day. Your IIF statement syntax is wrong.

The function Now() also include the date. 

Now() cannot be greater than 5 PM and less than 9:30 AM unless you have a date associated with them.  That is the same as saying >17 and <9.5 (17 being 24 hour clock).

   (Now() - Date())   removes the date.

Why not just use Now() in the field?

Answer 2

You are trying to compare a date value to a text string. In addition. Now returns a Date and Time. finally, the syntax for IIF() is IIF(condition,True value, False value). Try using:

IIF(Time()>#5:00 PM#,value,IIF(Time().....

If you are trying to check for a time range use:

IIF(Time()>#5:00 PM# AND Time() < #9:30 PM#,true, false)

Answer 3

Perfect, thank you so much!  Something so simple had me stump for a couple of hours today.

Answer 4

1. You can't just compare to now because it includes the date as well as time.  So you would want to use TimeValue(Now())
2. You don't use quotes for the time, you use Octothorpes (#):

#5:00:00 PM#