Summing a calculated field and evaluating it before report is shown?

[allie357]

I know that you cannot sum a calculated field in a query, and you can
do so on a report, but can you evaluate the report field to decide
which records are included?

I have a field that is counted in a query and it needs to be evaluated
at before the report is created. Only the records belonging to a person
with over a certain number of violations need to be shown. However with
my report I needed to do some ugly joining in the query to make it show
each records description as well as include the count, so i have to
join two queries.
Is there some way I can just have the report show the records whose sum
of the calculated field is equal to a number entered through a form
after the filter is applied?

Code:
--------------------------------------------------------------------------------

SELECT qry_Number_of_Violations_details.ViolatorName,
[qry_Violations_by_Violator_by Number of Times].CountOfPolicy_ID,
qry_Number_of_Violations_details.Policy,
qry_Number_of_Violations_details.CountOfPolicy AS [Violation Count],
qry_Number_of_Violations_details.Amount,
qry_Number_of_Violations_details.Description,
qry_Number_of_Violations_details.RCName,
qry_Number_of_Violations_details.DeptName,
qry_Number_of_Violations_details.DateEntered,
qry_Number_of_Violations_details.ViolatorsDepartmentNumber,
qry_Number_of_Violations_details.Violation_ID

 

[Pieter Wijnen]

sure
use the cancel event (procedure) of the (detail) section of the report to
cancel the unwanted records from beeing displayed

ie

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount > 1 Then exit sub ' Can't be bothered with reformatting
issues
If Me.TheCountField < 3 Then ' This bloke deserves another chance
Cancel=True
End If
End Sub
--
Pieter Wijnen

My feeble Access pages (good links though)
http://www.thuleeng.com/access
When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca/
http://allenbrowne.com/
http://www.lebans.com/

I know that you cannot sum a calculated field in a query, and you can
do so on a report, but can you evaluate the report field to decide
which records are included?

I have a field that is counted in a query and it needs to be evaluated
at before the report is created. Only the records belonging to a person
with over a certain number of violations need to be shown. However with
my report I needed to do some ugly joining in the query to make it show
each records description as well as include the count, so i have to
join two queries.
Is there some way I can just have the report show the records whose sum
of the calculated field is equal to a number entered through a form
after the filter is applied?

Code:
--------------------------------------------------------------------------------

SELECT qry_Number_of_Violations_details.ViolatorName,
[qry_Violations_by_Violator_by Number of Times].CountOfPolicy_ID,
qry_Number_of_Violations_details.Policy,
qry_Number_of_Violations_details.CountOfPolicy AS [Violation Count],
qry_Number_of_Violations_details.Amount,
qry_Number_of_Violations_details.Description,
qry_Number_of_Violations_details.RCName,
qry_Number_of_Violations_details.DeptName,
qry_Number_of_Violations_details.DateEntered,
qry_Number_of_Violations_details.ViolatorsDepartmentNumber,
qry_Number_of_Violations_details.Violation_ID