Comparing Dates in nested IIF statements

[Purdue_Engineer]

I want to return a value based on a date being within a date range. For example, if a date is between 01/01/12 and 01/31/12, the expression returns "1", if the date is between 02/01/12 and 02/29/12 , then "2", etc. I am using: IIf([ActualPickupDate]=Between #01/01/12# And #01/31/12#,1, IIf([ActualPickupDate]=Between #02/01/12# And #02/29/12#,2,... And #12/31/12#, 12,""))))))))))))

I keep getting syntax errors. Any suggestions?

Thanks

[rpeare]

Just a quick note here if you set your query up this way you're going to have to change the formulas every year to match the current year which is going to be a ton of maintenance but you don't need the = sign after [actualpickupdate] it would just be:

iif([ActualPickupDate] between #1/1/2012# and #1/31/2012#, 1, iif([actualpickupdate] between #2/1/2012# and #2/29/2012#, 2, etc...))

[Purdue_Engineer]

Ok thanks for the info.

I see that would be a lot of maintenance. Could you recommend a way to get around having to update the query for every year?

[rpeare]

If you're just trying to extract the month it's much more simple:

datepart("m", [ActualPickupDate])

[Purdue_Engineer]

That would work great. I am trying to use that in the same table as a Calculated Field but the expression is not allowed. Can I not use this in a table?

[rpeare]

I don't know what you mean, tables should not be storing calculated values, ever. You would use this formula in a query or form then use it to look up whatever else you wanted. If I'm reading this right you need to know what the month is to look up another value and then perform a calculation based on that looked up value? That can all be done with queries and storing a calculated value in a table is a horribly bad idea.

[Purdue_Engineer]

Ok I will put into a query.