Replace Null with 'currency zero'?

[bassman197]

Hi all,
I have always relied on Design View to create my queries, usually with great success, but in so doing, I have not become familiar with SQL view, so can't solve this problem:

I have a very simple query whose only function is to sum the payment amounts received for each contract number:

SELECT Payments.[Contract Number], Sum(Payments.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];

It is then used in subsequent queries to create an accounts payable report. It works great as long as at least one payment has been received on the contract, but if NO payments have ever been received, the Sum is actually null, so that record doesn't get included. I need a way for this query to return zero or currency zero 0.00 for all contracts with no payments yet, so those contracts will be included and the next query in line will see a zero.

In my search, it seems I need to make use of the IsNull function in my SQL (the check expression would be the sum of payments, the replacement value would be zero) OR I need to include a WHEN or WHERE in my SELECT statement above? I just don't know enough about SQL to make this happen - any help appreciated. Thanks!

 

[michaeljryan78]

SELECT Payments.[Contract Number], Sum(NZ(Payments.[Payment Amount],0)) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];

 

[Brianwarnock]

Will there be a record if no payment has been received?

Brian

 

[bassman197]

oops, should have said "accounts receivable". In other words, anyone who owes us money gets included, so any contract that has so far received zero payments (a null) needs to be included as a zero!

 

[bassman197]

michaeljryan78, thanks for the code. But, when I insert this into the query, and run it, the list I get still looks like only contracts that have received at least one payment (there are no zero results, which is what I'm looking to include, as well as the 'partial payment' contracts). I just copied and pasted, replacing the whole SQL field with your code.

 

[Brianwarnock]

If there is a record then the account field being currency or at least a number should default to 0, but if it is indeed null then Michael has provided the solution.

Brian

 

[Brianwarnock]

Ah! As I suspect from your last post there is indeed no record, I suspect that there is more to this, you probably have another table listing the contracts, this needs to be outer joined to this table so that you pull all records from that table and only matching records from the payment table, then you will have contracts with a null amount field to apply michael's code to.

Brian

 

[bassman197]

Looking at the Payments table, I see that the "Payment Amount" field is formatted as currency and the Default Value is 0. That would seem to indicate that I shouldn't be getting any nulls, but those records where no payments have yet been received are just not getting included...

 

[Brianwarnock]

Ok, so forget post 7, but it is rather a puzzle, I don't know what to say.

Can you post your DB. Or is it too confidential, it's getting late here and I'm not about tomorrow but somebody else may pick it up.

Brian