iif statement in access with multiple criteria

[akosc1]

Hello all,
I am very new to Access. So new that I'm currently creating my first database (in Access). I've done a bit of research, found a post on here that helped me with my syntax, but I'm still not able to get this query to work.

What I'm trying to do:
If the amount of days between the violation date and today is <= 180 then return the number 3, if its between 181 and 365 days then 2, if it's between 366 and 730 days then 1 else 0.

Here's my expression:
IIf(Date()-[Violation Date] < 180, 3,IIf(Date()-[Violation Date] Between 181 And 365, 2, IIf(Date()-[Violation Date] Between 366 And 730, 1, 0)))

Any help would be appreciated. Also, if I'm posting this in the wrong section of the forum, I truly apologize and direction towards the right section would be appreciated.

Thank you very much for your time.

 

[pbaldy]

More like:

IIf(Date()-[Violation Date] < 180, 3,IIf(Date()-[Violation Date] <365, 2, IIf(Date()-[Violation Date] < 730, 1, 0)))

The testing stops after the first "True" result, so you can just test for less than.

 

[Mark_]

Try this.

IIf(Date()-[Violation Date] < 180, 3,IIf(Date()-[Violation Date] < 365, 2, IIf(Date()-[Violation Date] < 730, 1, 0)))

You don't need between as your previous IFF's will catch the lower values.

 

[Mark_]

Paul,

You were slightly faster in posting!

 

[pbaldy]

It's the high altitude: less wind resistance on the fingers.

 

[akosc1]

Well the calculation works so that's awesome, thank you very much for the quick responses. Sorry for the noob follow up question, but why can I not use this expression for a calculated column?

 

[pbaldy]

I don't use them, but if memory serves the Date() function can't be used in one.

 

[Mark_]

Between would not be relevant.
You are asking "Is this less than 180 days"
You are then asking, "If its not less than 180 days, is it less than 365".

When you had BETWEEN, the logic would have EXCLUDED a value of exactly 180 days as you were looking for less than 180, then between 181 and 365.

To use BETWEEN, you would have to wrap it in the EVAL function.