Value based upon number range

[Evan Robitaille]

I am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was:
< 60 Days "30-59 days"
< 90 Days "60-89 Days"
<120 Days "90-119 Days"
>=120 Days "120 Days +"

It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish.
Thanks for any help

 

[raskew]

Hi -

The Switch() function may provide the desired result. Here's a query you can modify with your table and field names:

SELECT
    YourTable.Item
  , YourTable.YourDate
  , Int(DateDiff("d",[YourDate],Date())/30) AS Dayz
  , Switch([dayz]=1,"30-59 days",[dayz]=2,"60-89 days",[dayz]=3,"90-119 days ",[dayz]>=4,"120+ days",True,"") AS Expr2
FROM
   YourTable;

HTH -Bob

 

[pbaldy]

Your understanding of the IIf() is incorrect. It can return the text values you desire.