# Thread: AccesS Formula help Thanks: 0 Likes:  1 Post #5343648 (1)

1. ## AccesS Formula help

I could use some help with how to write this formula in ACCESS to determine age bucket for the column DaysAged. It works fine when I input just the formula for <= 30 days but, when I add 30-60, 60-240, and > 240 it isn't calculating correctly. I'm just not sure how to show the days between 30- 60 and 61 -240 ranges. Any help would be appreciated. Here is the formula I was trying to use.

NTG AGE BUCKET: IIF([DaysAged]<=30,"<30 Days",IIF([DaysAged]>30<=60,"31-60 Days",IIF([DaysAged]>60,<=240,"61-240 Days",IIF([DaysAged]>240,">240 Days"))))

2. ## Re: AccesS Formula help

If you have multiple conditions you have to separate with 'And' or 'Or' --

[DaysAged]>30 And [DaysAged] <=60, so it would like something like this --

IIF([DaysAged]<=30,"<30 Days",IIF([DaysAged]>30 And [DaysAged] <=60,"31-60 Days",IIF([DaysAged]>60 And [DaysAged] <=240,"61-240 Days",IIF([DaysAged]>240,">240 Days"))))

3. ## Re: AccesS Formula help

Great!! Thanks for the help.

4. ## Re: AccesS Formula help

Is the last IIF actually needed?

Originally Posted by CPGDeveloper
If you have multiple conditions you have to separate with 'And' or 'Or' --

[DaysAged]>30 And [DaysAged] <=60, so it would like something like this --

IIF([DaysAged]<=30,"<30 Days",IIF([DaysAged]>30 And [DaysAged] <=60,"31-60 Days",IIF([DaysAged]>60 And [DaysAged] <=240,"61-240 Days",IIF([DaysAged]>240,">240 Days"))))

5. ## Re: AccesS Formula help

I find the switch function is a clearer and neater option for things like this. If DaysAged returns and integer then:

Code:
Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60 Days",[DaysAged]<241,"61-240 Days",True,">240 Days")
Note the final True as an argument, this is the equivalent of an Else (if none of the other criteria evaluate to true then this one will and therefore needs to be the last one).

6. ## Re: AccesS Formula help

in a like vein:
Code:
Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60 Days",[DaysAged]<241,"61-240 Days",[DaysAged]>=241,">240 Days")