How do I specify a default value in a MS Access query?

Question

I have three tables similar to the following:

tblInvoices: Number | Date | Customer
tblInvDetails: Invoice | Quantity | Rate | Description
tblPayments: Invoice | Date | Amount

I have created a query called exInvDetails that adds an Amount column to tblInvDetails:

SELECT tblInvDetails.*, [tblInvDetails.Quantity]*[tblInvDetails.Rate]* AS Amount
FROM tblInvDetails;

I then created a query exInvoices to add Total and Balance columns to tblInvoices:

SELECT tblInvoices.*,
  (SELECT Sum(exInvDetails.Amount) FROM exInvDetails WHERE exInvDetails.Invoice = tblInvoices.Number) AS Total, 
  (SELECT Sum(tblPayments.Amount) FROM tblPayments WHERE tblPayments.Invoice = tblInvoices.Number) AS Payments,
  (Total-Payments) AS Balance
FROM tblInvoices;

If there are no corresponding payments in tblPayments, the fields are null instead of 0. Is there a way to force the resulting query to put a 0 in this column?

Answer 1

Use the nz() function, as in nz(colName, 0). This will return colName, unless it is null, in which case it will return the 2nd paramter (in this case, 0).