AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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])
