Need Accurate Amount Due

[mabyn]

I've created a query that does "almost" what I want. The way it is set up is:
Column One
COMPANYNAME
Column Two
REGISTRATIONFEE
Column Three
PAYMENTAMOUNT (what's been paid)
Column Four
AMOUNTDUE (Should be RegistrationFee less PaymentAmount)

It works in every instance where the Company paid something but for the ones
that they have not paid, it puts nothing. I need for Column Four to be what
is really due. If someone can help me, I would appreciate it. Thank you.

SELECT [Attendees].[CompanyName], [Payments].[PaymentAmount],
[Registration].[RegistrationFee], ([RegistrationFee]-[PaymentAmount]) AS
AmountDue
FROM Attendees LEFT JOIN (Registration LEFT JOIN Payments ON
[Registration].[RegistrationID]=[Payments].[RegistrationID]) ON
[Attendees].[AttendeeID]=[Registration].[AttendeeID];

 

[Allen Browne]

Use Nz() to substitute zero if the field is null.

This kind of thing:
SELECT [Attendees].[CompanyName],
[Payments].[PaymentAmount],
[Registration].[RegistrationFee],
Nz([RegistrationFee],0) - Nz([PaymentAmount],0) AS AmountDue
FROM ...

 

[Jeanette Cunningham]

Hi mabyn,
when the payment amount is null, it causes a problem with the calculation.

Use the Nz function to change nulls to 0.
Math can handle zeros, but not null.

(Nz([RegistrationFee],0)-Nz([PaymentAmount],0)) AS
AmountDue

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia