Do not print report if total is zero

[FJM]

I have a report based on an expression which shows me a financial value for individual items and then summarises the individual values to give a total for the report .
To preview the report the user enters a start and finish date via a calendar.
I want a message to appear and the report not to print if the total value of the items is ZERO - similar to the warning message you can receive from the ON NO DATA option


Anyone have any ideas ?

[ranman256]

check the count 1st...

if Dcount("*","qsQrySum") = 0 then
   MsgBox  "No records produced.", vbInformation, "Print Canceled"
else
   DoCmd.OpenReport "rTotals", acViewPreview
endif

[FJM]

At the moment, the report is opened up by a command button which has an embedded macro linked to the "On click" action.
Where would I enter your code

[FJM]

Can you also help with the code
The report name is
NCR Low Value COST

wITHIN the report, the Total cost expression is
=Sum(nz([Credit amount],0)+nz([NEWITEMCOSTTOCPE],0)+nz([CARRIAGEOUTWARD],0)+nz([COLLECTIONCOST],0)-nz([CREDITFROMCARRIER],0)-nz([COSTOFRETURNEDSTOCK],0))

Can you now re-write your code.
Also, would I cancel the embedded macro and simply replace it with your code ?
Thank you

[ranman256]

Remove the macro..
in the box where the macro goes, to the right is an elipsis box [...]
click it to goto code
paste inside the OnClick event.

[rpeare]

Ranman's code will work if you have ONE field in your source query that has the value you want, which it doesn't. Your 'check' value is a sum of multiple rows in your query, probably by an order number or something so you would have to account for that in your domain function or it will give you unexpected results. Ranman's suggestion is also COUNTING rows, not SUMMING rows which will really give you the wrong results as I would imagine, again you have multiple rows per order.

so you can do one of two things.
1. Create a query that has the calculation you want and use ranman's suggestion but count the items in that summarized query
or
2. Create a domain function that addresses your calculation need for instance:

dsum("nz([Credit amount],0)+nz([NEWITEMCOSTTOCPE],0)+nz([CARRIAGEOUTWARD],0)+nz([COLLECTIONCOST],0)-nz([CREDITFROMCARRIER],0)-nz([COSTOFRETURNEDSTOCK],0)", "SOURCEQUERY")

if your source query has multiple rows but it's all related to the same item you're reporting on this would work.
If the source query has multiple rows but they are NOT all related to the same item you're reporting on you'd have to add a 'where' clause to your domain function