Formula needed

[Leesha]

Hi,
I'm trying to do a conditional statement in an Access query. I have [TotalDaysOut} which can range from 1 to any number. I need to break it into groups of 30days, 60days, 90days, 120days, 160days and greater than 160 days. I tried Aging:iif([totaldaysout]<31,"30days") which worked fine. Once I tried adding in the other conditions I got stuck.

Thanks,
Leesha

[HansV]

Try

Aging: Switch([totaldaysout]<31, "30days", [totaldaysout]<61, "60days", [totaldaysout]<91, "90days", [totaldaysout]<121, "120days", [totaldaysout]<161, "160days", [totaldaysout]>160, "greater than 160days")

[Leesha]

That did it! I've never seen/used "switch". What other types of instances would I use it?

Leesha

[HansV]

The Switch function can be used in expressions in queries and in the control source of controls on a form or report if you need to test multiple conditions. It is the equivalent of a series of nested IIf functions. The syntax is

Switch(condition1, value1, condition2, value2, condition3, value3, ...)

The function returns the value that corresponds to the first condition that is true. For example, if condition1 is false but condition2 is true, Switch will return value2.
If none of the conditions is true, the function returns Null.

See Switch Function.