put zero amts query if data is null

[novice_techy]

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(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;

sample of how report looks like:
http://www.geocities.com/[email protected]/report.JPG

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??

 

[fredg]

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(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;

sample of how report looks like:
http://www.geocities.com/[email protected]/report.JPG

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??

Look up the Nz function in VBA help.

Exp:Nz([FieldName1],0) + Nz([FieldName2],0) + etc.

 

[novice_techy]

Works great when I execute it from an ms access query. However, I tried
it in data environment in visual basic 6 for the data report in the sql
statement field but I cannot get it to execute correctly when I apply
it.

 

[novice_techy]

In vb 6.0 data environment it does not get accepted:

SELECT gas_bill.spaceno, Max(gas_bill.name_gas) AS name_gas,
Max(gas_bill.total_due) AS total_due, Sum(Nz([check_amt],0)) AS
check_amtcopy, Sum(Nz([cash_amt],0)) AS cash_amtcopy,
(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;

 

[Douglas J. Steele]

You cannot check if a value is Null using =: you must use the IsNull
function.

Rather than

IIf(receipts.check_amt=NULL,0,receipts.check_amt)

use

IIf(IsNull(receipts.check_amt),0,receipts.check_amt)

 

[John Spencer]

You may be able to use the IIF statement

IIF([FieldName1] Is Null,0)

If not, post back.

 

[novice_techy]

Thanks 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;

 

[novice_techy]

Thanks 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;