jheddings jheddings - 2 months ago 7
SQL Question

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

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

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).