How do I specify a default value in a MS Access query?
- by jheddings
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?
