Help with Access IF Statement

[VBABeginner3]

I'm in query design and I entered this function into "Field" but it's not working. Why? Age-CRD is a calculated field. I'm trying to show the result as a range.

DisplayText: IIf([Age-CRD]<=5,"0-5",IIf([Age-CRD]<=10,"6-10",IIf([Age-CRD]<=30,"11-30",IIf([Age-CRD]<=60,"31-60",IIf([Age-CRD]<=90,"61-90",IIf([Age-CRD]<=120,"91-120",IIf([Age-CRD]<=180,"121-180",IIf([Age-CRD]<=365,"181-365",0))))))))

 

[JANR]

This is better suited for the Switch() function.

ex:

DisplayText: Switch([Age-CRD]<=5,"0-5",[Age-CRD]<=10,"6-10", .... ,[Age-CRD]>365,0)

Good luck

Jan R

*edit Wrong final argument

 

[plog]

When you get that nested, its time for a function. Or a table.

Function method:

Create a new function in a module and use as many lines as you want to write your code instead of cramming it indecipherably into one line. Something like this:

Function get_AgeRange(in_Age) As String
    ' takes in_Age and converts it to the range it falls in

ret ="Error"    ' return value, default is error message

if(in_Age<=5) then ret = "0-5"
if (in_Age>5) AND (in_Age<=10) then ret = "6-10"
  ' range logic above

get_AgeRange = ret
End Function

Table method:

Create a 3 column table with your ranges, like so:

tblAgeRanges
RangeMin, RangeMax, RangeName
0, 5, "0-5"
6, 10, "6-10"
etc.

Then you bring the table into your query, JOIN the [Age-CRD] field to both the RangeMin and RangeMax fields, bring down RangeName field to show in the query. Then you switch to SQL view and manually change the JOINS for tblAgeRanges to something like so:

...ON [Age-CRD]>=RangeMin AND [Age-CRD]<=RangeMax...

 

[VBABeginner3]

JANR, it didn't work. I went back to using IF and it keeps asking me for the age-crd parameter

plog,
I'm putting this in an Access query.

 

[plog]

I don't understand your comment to me. My post required no clarification. I gave you 2 methods to achieve what you want.

 

[VBABeginner3]

This is what I have now. It's not working. The switch didn't work.

DisplayText: IIf([Age-CRD]<=5,"0-5",IIf([Age-CRD]<=10,"6-10",IIf([Age-CRD]<=30,"11-30",IIf([Age-CRD]<=60,"31-60",IIf([Age-CRD]<=90,"61-90",IIf([Age-CRD]<=120,"91-120",IIf([Age-CRD]<=180,"121-180",IIf([Age-CRD]<=365,"181-365",IIf([Age-CRD]>365,"Over 365",0)))))))))

AGE-CRD is a datediff calc. Does that have anything to do with it? I thought number of days it returned was an integer..

 

[VBABeginner3]

plog,
I don't want this in a VBA. I want it as a calculation in the query for a number of reasons because many actions occur after the query runs. Thank you.

 

[VBABeginner3]

OMG. My expression was correct. It was just showing as GROUP BY in design view instead of EXPRESSION.

RESOLVED. Thanks everyone for trying to help.

DisplayText: IIf([Age-CRD]<=5,"0-5",IIf([Age-CRD]<=10,"6-10",IIf([Age-CRD]<=30,"11-30",IIf([Age-CRD]<=60,"31-60",IIf([Age-CRD]<=90,"61-90",IIf([Age-CRD]<=120,"91-120",IIf([Age-CRD]<=180,"121-180",IIf([Age-CRD]<=365,"181-365",IIf([Age-CRD]>365,"Over 365",0)))))))))

Best,