Calculate days and catagorize in report

[mkelly]

I have a database the logs [start date] and then [test date]. I want to run a query for a report that will calculate how many days between [start date] and [test date] and then report it in 4 catagories on a report.

for example
if it is greater than 1 but less than 30 I want it to be catagory 1
if it is equal to 30 but less than 60 I want it to be catagory 2
if it is equal to 60 but less than 90 I want it to be catagory 3
if it is equal to 90 (or [test date] is null) I want it to be catagory 4

Any help greatly appriciated.

Thanks

 

[FLabrecque]

Have you looked at the function DateDiff? It would be a great start. Also, try searching the forum. You should find a couple of nice answers to this.

 

[mkelly]

thanks, I know how to figure the days what I am having trouble with is getting it to report a disserent catagory on the report based on the amount of days.

 

[spowers21]

Create a field in your querie with a nested IIF statement. Example: Category:IIF([StartDate]-[Testdate] between 1 and 29,1,IIF([StartDate]-[TestDate] between 30 and 59,2,IIF([StartDate]-[Testdate] between 60 and 89,3,IIF([StartDate]-[Testdate]=90,4,IIF([StartDate]-[TestDate] Is Null,4)))))

 

[mkelly]

Thanks for the help!

 

[Mile-O]

Create a function with a SELECT CASE structure - it's faster than all those immediate ifs in one expression.