Handling nulls found in expression

[CW]

I have a query (qryOrderData) that lists all our Orders and shows a variety
of data including InvoiceValue.
InvoiceValue is an expression that comes from another query
(qryOrderInvoices)which looks at tblInvoices and groups them by OrderNo to
give me a total that has been invoiced on each order.
Trouble is, if an order hasn't been invoiced yet it obviously isn't included
in qryOrderInvoices.
And then when qryOrderData tries to look for InvoiceValue on those orders it
doesn't find anything of course, and shows #Error.
I have tried using Nz in various ways but cannot get rid of the #Error. I
want it to show 0 in such cases.
My field is this: InvoiceValue: Expr1 (the source is qryOrderInvoices)
How should I modify it, please?
Many thanks
CW

 

[Ken Sheridan]

Include the Orders table in your qryOrderInvoices query by means of an
outer join on the OrderNo so that it returns all orders whether or not
yet invoiced, e.g.

SELECT tblOrders.OrderNo,
NZ(SUM(Amount),0) AS InvoiceValue
FROM tblOrders LEFT JOIN tblInvoices
ON tblOrders.OrderNo = tblInvoices.OrderNo
GROUP BY tblOrders.OrderNo;

Ken Sheridan
Stafford, England