If a value falls between 'this time' and 'that time'...

[tsteele2000]

I'm writing a query and I want to write an expression that looks at a time field, and if that time falls within a certain period, returns a value.

Something along the lines of:

if(8:59:00 AM>[TimeIn]>11:00:00 AM),1,ELSEIF(10:59:00 AM>[TimeIn]>1:00:00 PM,2,ELSEIF(12:59:00 PM>[TimeIn]>3:00:00 PM,3,ELSEIF(2:59:00 PM>[TimeIn]>6:00:00 PM,4))))

So if the time is between 9 - 11, returns a value of 1
If the time is between 11 - 1, returns a value of 2, etc.

What is the syntax for creating a field based on this expression, in Access?

Thanks.

 

[GoDawgs]

You've got it basically, although you have you operands backwards (should be < < instead of > >)...just use &quot;iif&quot; instead of &quot;if&quot;, and you have to use AND instead of what you have...so the first part would be:

iif([TimeIn]>8:59:00 AM AND TimeIn]< 11:00:00 AM,1,iif(...

There's no need for ElseIf...just put another iif statement in the false portion of the previous iif. Hope that helps.

Kevin

 

[tsteele2000]

Thanks...following your example, I entered the following syntax to create a field called &quot;Period&quot;:


Period: iif([lobbyTimeIn]>#8:59:00 AM# AND [lobbyTimeIn]< #11:00:00 AM#,1,iif([lobbyTimeIn]>#10:59:00 AM# AND [lobbyTimeIn]< #1:00:00 PM#,2, iif([lobbyTimeIn]>#12:59:00 PM# AND [lobbyTimeIn]<# 3:00:00 PM#,3, iif([lobbyTimeIn]>#3:59:00 PM# AND [lobbyTimeIn]<# 6:00:00 PM#,4))))


I'm still getting a syntax error - can you help me troubleshoot?

Thanks.

 

[GoDawgs]

Not sure...when are you getting the error? What does it say? You're missing the 3-4 hour...not that that would give you a syntax error, but thought I'd mention it.

 

[tsteele2000]

(BTW, thanks for pointing out the logic error, I'll fix that!)

I fixed the error - I clicked over to the SQL side and the query design was generating some funky SQL code, so I edited it by hand and it works now. (I should have just written the SQL in the first place - so much for shortcuts!)

Thanks again.

 

[GoDawgs]

No problem, glad to help.