#Error because no records foung in query

[Matt Reed]

Run a query for each salesman (xyz) show every sale for a given month
xyzSale,

Run a report (xyzMonthlySales) for each salesmen from their query that list
each sale xyzSale times his commision rate xyzRate and that salesmens
commision on that sale xyzCommAmount, at the bottom of the report I total
the commision for that salesman. Sum(xyzCommAmount)

Then use a form to show each of the salesmens total commision for the month
pulled from the reports, total those numbers and give the user a chance to
enter a record in a different table showing amounts of each salesmens
commision and the total commision paid each month.

This all works expect when any one (or more) salesman has no sales for the
month.
Then when the query for that salesman is run - no records are found.
The Report shows #Error in the line item for that salesmen and the total for
the month Sum(xyzCommAmount).
This filters down to the form which will not give me a total for all sales
because it shows #Error for that one saleman.

Found a post in this fourm that uses recordcount on a sub form
**
For a subform control named MySF, set the ControlSource of the textbox
on this main form like this:
=IIF(MySF.Form.Recordset.RecordCount=0,0,MySF.Form.Fieldname)
**
Can I use recordcount to set Sum(xyzSales) on the report to zero if no
records are found for that salesman?
If so what would be the syntax


Any other suggestions are welcomed


Matt

 

[Marshall Barton]

Run a query for each salesman (xyz) show every sale for a given month
xyzSale,

Run a report (xyzMonthlySales) for each salesmen from their query that list
each sale xyzSale times his commision rate xyzRate and that salesmens
commision on that sale xyzCommAmount, at the bottom of the report I total
the commision for that salesman. Sum(xyzCommAmount)

Then use a form to show each of the salesmens total commision for the month
pulled from the reports, total those numbers and give the user a chance to
enter a record in a different table showing amounts of each salesmens
commision and the total commision paid each month.

This all works expect when any one (or more) salesman has no sales for the
month.
Then when the query for that salesman is run - no records are found.
The Report shows #Error in the line item for that salesmen and the total for
the month Sum(xyzCommAmount).
This filters down to the form which will not give me a total for all sales
because it shows #Error for that one saleman.

Found a post in this fourm that uses recordcount on a sub form
**
For a subform control named MySF, set the ControlSource of the textbox
on this main form like this:
=IIF(MySF.Form.Recordset.RecordCount=0,0,MySF.Form.Fieldname)
**
Can I use recordcount to set Sum(xyzSales) on the report to zero if no
records are found for that salesman?

In AXP and A03, you probably cam do that in a report, but I
suggest that in report's you use:

=IIf(mysr.Report.HasData, mysr.Report.totaltextbox, 0)

 

[Matt Reed]

Thanks for the reply, it got me on the right track.

Matt