AccesS Formula help

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
71
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

Board Regular
Joined
Oct 8, 2008
Messages
123
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"))))
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
707
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

Active Member
Joined
Jul 16, 2010
Messages
471
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",[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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,576
Office Version
2013
Platform
Windows
in a like vein:
Code:
Switch([DaysAged]<31,"<30 Days",[DaysAged]<61,"31-60 Days",[DaysAged]<241,"61-240 Days",[COLOR="#FF0000"][DaysAged]>=241[/COLOR],">240 Days")
 

Forum statistics

Threads
1,084,842
Messages
5,380,226
Members
401,657
Latest member
Halley

Some videos you may like

This Week's Hot Topics

Top