Zero versus Null Confusion Maybe? (1 Viewer)

DeanFran

Registered User.
Local time
Today, 04:58
Joined
Jan 10, 2014
Messages
111
I’ve built a simple inventory tracking tool for our manufacturing department. Our products are manufactured in discrete batches, and the number of shippers produced is entered. Then shippers are sent out as customers require. I used two tables, the Batch table BatchT, and a Shipped Quantity Table tblShipQuantity (I changed my naming convention during the design of this database as you can see). I set up a continuous form via a couple queries.

qryShippedSum
Code:
SELECT Sum(tblShipQuantity.ShippedAmount) AS SumOfShippedAmount, tblShipQuantity.BatchID
  FROM tblShipQuantity
  GROUP BY tblShipQuantity.BatchID;
qryInvTrackList
Code:
SELECT BatchT.BatchNumber, BatchT.BatchID, BatchT.NumberOfShippers, BatchT.ReadyForSaleID, Nz([qryShippedSum].[SumOfShippedAmount],0) AS SumOfShippedAmount, ProductT.ProductName, [NumberOfShippers]-[SumOfShippedAmount] AS Balance, BatchT.DispositionID, BatchT.LotNumber
  FROM (BatchT LEFT JOIN qryShippedSum ON BatchT.BatchID = qryShippedSum.BatchID) INNER JOIN ProductT ON BatchT.ProductID = ProductT.ProductID
  WHERE (((BatchT.ReadyForSaleID)=1) AND ((BatchT.DispositionID)=1));


As you can see qryShippedSum is included in qryInvTrackList. I wanted the continuous form to show a zero in the SumOfShippedAmount Column if there was no activity yet, so I used the Nz([blah.blah],0) thing above to display a zero, as shown in the attached image.


Everything works, but I would like to exclude batches that have a zero balance in the continuous form. I tried setting the criteria for Balance ([NumberOfShippers]-[SumOfShippedAmount] AS Balance) to > 0, but when I do that, any batches that have a zero for the Total Shipped (SumOfShippedAmount in the query) column are also excluded. I’m guessing it has something to do with nulls, versus zero, that is presently beyond my understanding. Any help is appreciated.
 

Attachments

  • ListCap.PNG
    ListCap.PNG
    3.3 KB · Views: 30

Minty

AWF VIP
Local time
Today, 08:58
Joined
Jul 26, 2013
Messages
10,353
You've referred to SumOfShippedAmount in the NZ calulation, and then called your result that as well (I'm surprised Access doesn't complain tbh)

However you then refer to that in your Balance Calculation as well - but it will be using the original value, not the aliased ( Nz() ) one. So your null values will still be there and you can't add anything to a null, hence your missing results.
 

DeanFran

Registered User.
Local time
Today, 04:58
Joined
Jan 10, 2014
Messages
111
Homer Simpson Duh! *Pounding my head on desk*. Thank you.
 

Users who are viewing this thread

Top Bottom