Expert: Scottgem - 2/23/2005

Question
I am trying to compose a report based on a query using Access 2000. The query returns the amount of time that a person has spent here, with most of the results being between 0 and 180 mins, but inevitably there are some that are outside the normal range. Also note that the results are in integer form and not date/time. What I would like to do is divide the results into different intervals in the report - for example in groups going up by 20. This I can do, but I would like also to have a catch-all at the end - like 180+ - to include all of the other values rather than wasting space cataloging each one. Here is what I have so far - thanks in advance for all of your help.
=Int(([TimeElapsed])/10)*10 & " - " & Int((([TimeElapsed])/10)+2)*10-1 & "  mins" - this is for the interval - on the right

=Sum([TimeCount]) this is the value the interval describes


Answer
I'm not sure I follow what you have done, but I would do it with a custom function.

Public Function TimeInterval(intTimeElapse As Integer)

Select Case intTimeElapse
Case <= 20
TimeInterval = "0-20"

Case Between 21 AND 40
Time Interval = "21-40"

etc.

Case >180
Time Interval = "> 180"

End Select
End Function

To use this, add a column in your query with the expression:
TimeInt: TimeInterval([TimeElpased])

HTH
Scott<>