make checkbox in report return Zero

[mseo]

hi,
I have a report connected to a form where I can print the invoices between the two dates in the form but I have the problem:
when the checkbox = true refers to Canceled invoice but the values in this record still as it is, and I want to make the values equal zero if the checkbox = true, I tried hardly to make them equal zeros but the record disappeared from the report
I want to include the record within the report equal zero even it has a value in the query if the checkbox = true
I used this SQL Statement:

SELECT tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, CCur(Sum(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*(nz(tbl_SalesInvoicesDetails.Quantity,0)))) AS Extended, CCur(Sum((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)) AS [Tax 10%], CCur(Sum((((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))+(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)))) AS [Grand Total], tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled
FROM (tbl_Customer INNER JOIN tbl_SalesInvoices ON tbl_Customer.CustomerID=tbl_SalesInvoices.Customer_ID) LEFT JOIN tbl_SalesInvoicesDetails ON tbl_SalesInvoices.InvoiceNo=tbl_SalesInvoicesDetails.InvoiceNo
GROUP BY tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled, tbl_Customer.CustomerID
HAVING (((tbl_SalesInvoices.Date) Between Forms![Search Sales Journal]!DF And Forms![Search Sales Journal]!DT) And ((CCur(Sum(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*(nz(tbl_SalesInvoicesDetails.Quantity,0)))))=IIf(tbl_SalesInvoices.Cancelled=True,0,CCur(Sum(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*(nz(tbl_SalesInvoicesDetails.Quantity,0)))))) And ((CCur(Sum((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)))=IIf(tbl_SalesInvoices.Cancelled=True,0,CCur(Sum((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)))) And ((CCur(Sum((((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))+(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)))))=IIf(tbl_SalesInvoices.Cancelled=True,0,CCur(Sum((((nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))+(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*nz(tbl_SalesInvoicesDetails.Quantity,0))*10/100)))))))
ORDER BY tbl_SalesInvoices.InvoiceNo;

thanks in advance for any help you may provide me

[nico5038]

You'll need an IIF like:

IIF(tbl_SalesInvoices.Cancelled=True,0,CCur(Sum(nz(tbl_SalesInvoicesDetails.UnitPrice,0)*(nz(tbl_SalesInvoicesDetails.Quantity,0))))) AS Extended, ....etc
 

Nic;o)

[mseo]

thank you
but this statement get the canceled record out of the report because when I check the checkbox in the form to cancel the invoice, I want to make it equal zeros in the report not to be disappeared
it is now 11 days looking for any solution for this problem
thank you

[mseo]

oh sorry
it is working like the charm this is the real best answer
I appreciate your replies
thank you very much