Conditionally setting the value of an expression (1 Viewer)

[ChristyG]

I am trying to enter an experssion into the grid for age groups. The result will be a text value based on the value in a column named Age. There are three groups, '<18', '18-30', and '31+'. I tried using the following but the result that is returned is #Error.

age_grp: Switch([age]<18,'<18',[age]>=18 And [age]<31,'18 -30',[age]>30,'31+')

Do I have the syntax wrong? or maybe I'm going about it completely wrong.

I have also tried a nested iif statement Age2:= iif([age]<18,'<18', iif([age] >=18 And < 31, '18-30'), iif([age] >30, '31+')). I get the same error message.

This worked for displaying the value on a report =IIf([age]>=18 And [age]<=30,"18-30",'31+'). However, I need to sort and group by the value so I can count the number in each group, so I wanted to put it into a query.

Thanks,
Christy

 

[RuralGuy]

How about:
Age2:= IIf([age]<18,'<18', IIf([age]>30,'31+', '18-30')).

 

[ChristyG]

Thanks for the input, but that got me #Error also. Any other ideas?

Christy

 

[ChristyG]

I just realized that I made the assumption that Age is defined as an integer. It is coming from a table in SQLServer that I didn't create. Guess I was wrong. It is a text field. I just changed my query to handle it as such and it is now working.

Thanks again for the Help. I think it made me start questioning some of my assumptions.

Christy

 

[RuralGuy]

Outstanding! Thanks for posting back with your success Christy.