range query

[deekras]

i have to find how many members are in each date range. meaning how many members have been active for 0-3 months, 4-8 months, 9-15 months, 16 - 24 months, 24+ months.

can i do this with a query. i don't want a lot of iif statements.

any suggestions?

 

[The_Doc_Man]

Depends on how far you want to take the query, but you might try this approach:

In a query, compare the date for start of activity vs. current date using DateDiff function returning units of months, divide that number by 4, and truncate it to an integer. Sort ascending based on that field in the query.

To show the actual months as well as the grouping, you would of course have to repeat the DateDiff in a second column for display purposes, but that is a minor point.