totalling points then reporting totals that are over a number

[Sue]

I created a database for a school which tracks infractions. These infractions
are stored in a table, and a student receives one point per infraction. When
I try to total points in a query using sum, it works fine. When I add a
parameter with a date range (e.g. infractions that occurred January - March)
then I cannot total them in the query, but can with a separate field in a
report. I would like to report on only those that are over 4 points within a
date range. I'm running into a brick wall in the reporting. How to filter
only those with over 4 points within a date range? Do I add something to the
query? Or add something to a macro that runs the report? Or some expression
within the report?
Thanks!

 

[Stockwell43]

Hi Sue,

If I am understanding you correctly, the follow is all you should need. Open
your query to Design View and place this code in the date field:
Between
IIf(IsNull([Forms]![frmDocPrompt]![BeginningDate]),#01/01/1990#,[Forms]![frmDocPrompt]![BeginningDate])
And
IIf(IsNull([Forms]![frmDocPrompt]![EndingDate]),Date(),[Forms]![frmDocPrompt]![EndingDate])

In the Infractions column of the query place this in the Criteria: >4

This should work. The code for the date is useful if you have a pop up form
for your report.

Good Luck!

 

[Stockwell43]

What I gave you earlier will show all records >4. If you want to total, I
would do that in your report.

 

[Sue]

There is something we are missing. Alll infraction points are equal to 1. I
can total in the query using the sum(infractionppoints) and get a total per
student. And I can add criteria there >4. But, as soon as I put in the date
parameter, I get no records returned in the query. It is as though Access
does the adding first, ignoring the dates, and cannot return a result when
the data parameters are added.
If I leave out the sum and use only the date parameter, then I get the
records in the date range. Then I can total in the report (not in the query).
But I only want to show those that are >4. How can I do this in the report?