add zero amts to null datareport fields 1

[JacobTechy]

I have the following sql code in the data environment in a data report in vb6 sp4 with ms access 2002.

SELECT gas_bill.spaceno, max(gas_bill.name_gas) as name_gas,
Max(gas_bill.total_due) as total_due, 
Sum(receipts.check_amt) as check_amt,
Sum(receipts.cash_amt) as cash_amt,
(Max(gas_bill.total_due) -Sum(receipts.check_amt)) as difflcheck, 
(Max(gas_bill.total_due) -Sum(receipts.cash_amt)) as difflcash
FROM receipts RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno group by gas_bill.spaceno

sample of how report looks like:
report sample

My problem is that I cannot get the the null check amts or cash amts to subtract unless they are $0.00. How can I add zeros to these fields if they are null to allow the calculation to take place for the non payed customer. I know that, in my receipts table I only have 3 records which is why the $0.00 for the rest of the records do not show up, but records are added as the customer pays either by cash or check. Can I add sql code??

 

[rmnastor]

USE ISNULL

iif(isnull(field),0,field)

eg..

SELECT gas_bill.spaceno, max(gas_bill.name_gas) as name_gas,
Max(gas_bill.total_due) as total_due,
Sum(iif(isnull(receipts.check_amt,0,receipts.check_amt)) as check_amt,
Sum(iif(isnull(receipts.cash_amt,0,receipts.cash_amt)) as cash_amt,
(Max(gas_bill.total_due) -Sum(iif(isnull(receipts.check_amt,0,receipts.check_amt)) ) as difflcheck,
(Max(gas_bill.total_due) -Sum(iif(isnull(receipts.cash_amt,0,receipts.cash_amt)) ) as difflcash
FROM receipts
RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno group by gas_bill.spaceno

 

[JacobTechy]

Thanks for the code but it did not work. I did have to modify the code a bit to get rid of errors. Any other ideas?

SELECT gas_bill.spaceno, max(gas_bill.name_gas) AS name_gas, Max(gas_bill.total_due) AS total_due, Sum(IIf(receipts.check_amt=NULL,0,receipts.check_amt)) AS check_amt, Sum(IIf(receipts.cash_amt=NULL,0,receipts.cash_amt)) AS cash_amt, (Max(gas_bill.total_due)-Sum(IIf(receipts.check_amt=NULL,0,receipts.check_amt))) AS diffl, (Max(gas_bill.total_due)-Sum(IIf(receipts.cash_amt=NULL,0,receipts.cash_amt))) AS difflcash
FROM receipts RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno
GROUP BY gas_bill.spaceno;

 

[JacobTechy]

Thank it worked for me after some trial and error:

SELECT gas_bill.spaceno, Max(gas_bill.name_gas) AS name_gas, Max(gas_bill.total_due) AS total_due, Sum(IIf(isnull(receipts.check_amt),0,receipts.check_amt)) AS check_amt1, Sum(IIf(isnull(receipts.cash_amt),0,receipts.cash_amt)) AS cash_amt, (Max(gas_bill.total_due)-Sum(IIf(isnull(receipts.check_amt),0,receipts.check_amt))) AS diffl, (Max(gas_bill.total_due)-Sum(iif(isnull(receipts.cash_amt),0,receipts.cash_amt))) AS difflcash
FROM receipts RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno
GROUP BY gas_bill.spaceno;

 

[rmnastor]

that's is nice, i hope i was of some help...
feel free to email me if u need more help...