AccesS Formula help

[bwlytkr]

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"))))

 

[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"))))

 

[bwlytkr]

Great!! Thanks for the help.

 

[welshgasman]

Is the last IIF actually needed?

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"))))

 

[stumac]

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

Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60 Days",[DaysAged]<241,"61-240 Days",[COLOR=#ff0000]True[/COLOR],">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).

 

[xenou]

in a like vein:

Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60 Days",[DaysAged]<241,"61-240 Days",[COLOR="#FF0000"][DaysAged]>=241[/COLOR],">240 Days")