Access problem - query with null fields

[ramesh]

I have an aggregate query with the following joining:

SELECT qItem.referenceno, Sum(FolioItems.qty) AS SumOfqty, First(FolioItems.JACIF) AS FirstOfJACIF, First(qItem.description) AS FirstOfdescription, First(qItem.folioid) AS FirstOffolioid, Sum([qty]-[soldqty]) AS balance
FROM (qItem LEFT JOIN SoldItems ON qItem.ID = SoldItems.itemID) INNER JOIN FolioItems ON qItem.ID = FolioItems.itemID
GROUP BY qItem.referenceno;

qItem.ID is joined to SoldItems.ItemID : 2. Include ALL items from qItem and only those from SoldItems where the joined fields are equal.

qty and soldqty are both Total:sum and I have a field called balance: [qty]-[soldqty]

Now this gives me a table that shows each item and how many sold, however where none is sold I get a null value, and this also gives me a null value for balance. How do I resolve this?

[angeleta]

I have an aggregate query with the following joining:

SELECT qItem.referenceno, Sum(FolioItems.qty) AS SumOfqty, First(FolioItems.JACIF) AS FirstOfJACIF, First(qItem.description) AS FirstOfdescription, First(qItem.folioid) AS FirstOffolioid, Sum([qty]-[soldqty]) AS balance
FROM (qItem LEFT JOIN SoldItems ON qItem.ID = SoldItems.itemID) INNER JOIN FolioItems ON qItem.ID = FolioItems.itemID
GROUP BY qItem.referenceno;

qItem.ID is joined to SoldItems.ItemID : 2. Include ALL items from qItem and only those from SoldItems where the joined fields are equal.

qty and soldqty are both Total:sum and I have a field called balance: [qty]-[soldqty]

Now this gives me a table that shows each item and how many sold, however where none is sold I get a null value, and this also gives me a null value for balance. How do I resolve this?

Not sure but - Try using a wildcard character before the expression and see if that helps...that should force the query to return non-null values...check also if one of your fields is a string...

[ramesh]

I found the solution: The NZ command makes any null (non zero) field zero.

SO I changed soldqty to =nz([soldqty]) and it worked out just fine.