CODEWITHSUNDEEP
sqlms-access
salisboss
salisboss

Reputation: 139

Access query - in Query Design GUI how do I set a sum to return zero if null?

I have a query that based on two inputs calculates some sums and then takes the difference of those sums. The sum PayAmountParts could be null if no parts have been provided. This messes up my balance owed and thus the query returns nothing. I have seen the VBA or SQL code to do this but nothing for doing it in the Design View GUI. Where in the Access Query design query can I set this to return 0 if null?

Edit: I changed the query design to have two subquerys that do the individual summing then a query that uses them to calculate the difference. The issue still remains that when either calculate is null ie no payback as yet the queries return empty cells across the board. I want the text to show the text data and the calculated cell to show 0. Here is the SQL code for the queries.

Here is the code to sum up the number of items sent out:

SELECT CustomerT.CustomerProgram, PartsT.PartID, PartsT.PartName, Sum(NZ(DiversionT.RcvdQTY,0)) AS SumOfRcvdQTY
FROM PartsT 
   INNER JOIN (CustomerT 
     INNER JOIN DiversionT ON 
        CustomerT.CustomerID = DiversionT.CustomerID) ON 
        PartsT.PartID = DiversionT.PartID
GROUP BY CustomerT.CustomerProgram, PartsT.PartID, PartsT.PartName
HAVING (((CustomerT.CustomerProgram)=[Forms]![PartsSearchF]![CustomerSearch]) 
      AND ((PartsT.PartID)=[Forms]![PartsSearchF]![PartSearch]));

This query calculates the number paid back:

SELECT CustomerT.CustomerProgram, PartsT.PartID, PartsT.PartName, Sum(NZ(PaybackT.PayAmountParts,0)) AS SumOfPayAmountParts
FROM PartsT 
    INNER JOIN (CustomerT INNER JOIN PaybackT ON CustomerT.CustomerID = PaybackT.CustomerID) 
      ON PartsT.PartID = PaybackT.PartID
GROUP BY CustomerT.CustomerProgram, PartsT.PartID, PartsT.PartName
HAVING (((CustomerT.CustomerProgram)=[Forms]![PartsSearchF]![CustomerSearch])
     AND ((PartsT.PartID)=[Forms]![PartsSearchF]![PartSearch]));

This query is supposed to calculate the difference.

SELECT SinglePartPaybackSumQ.CustomerProgram, PartsT.IndiaUID, PartsT.NSN, PartsT.PartName, SinglePartSumQ.SumOfRcvdQTY, SinglePartPaybackSumQ.SumOfPayAmountParts, [SumOfRcvdQTY]-[SumOfPayAmountParts] AS BalanceOwed
FROM (PartsT 
    INNER JOIN SinglePartPaybackSumQ ON PartsT.PartID = SinglePartPaybackSumQ.PartID) 
    INNER JOIN SinglePartSumQ ON PartsT.PartID = SinglePartSumQ.PartID;

Upvotes: 0

Views: 369

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Using an alias and an if statement in the query builder:

field alias: Iif(isnull([fieldname]), 0, [fieldname])

Upvotes: 1

Related Questions