Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2018, 05:45 AM   #1
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 86
Thanks: 49
Thanked 0 Times in 0 Posts
DeanFran is on a distinguished road
Zero versus Null Confusion Maybe?

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.
Attached Images
File Type: png ListCap.PNG (3.3 KB, 11 views)

DeanFran is offline   Reply With Quote
Old 05-17-2018, 06:11 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,526
Thanks: 132
Thanked 1,492 Times in 1,464 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Zero versus Null Confusion Maybe?

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
DeanFran (05-17-2018)
Old 05-17-2018, 07:00 AM   #3
DeanFran
Newly Registered User
 
Join Date: Jan 2014
Posts: 86
Thanks: 49
Thanked 0 Times in 0 Posts
DeanFran is on a distinguished road
Re: Zero versus Null Confusion Maybe?

Homer Simpson Duh! *Pounding my head on desk*. Thank you.

DeanFran is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] PHP versus Python Steve R. PHP & MySQL 2 04-12-2014 05:36 PM
Query Criteria ==> Is Null versus "" R_J_Potter@msn.com Queries 1 09-11-2012 02:56 PM
SQL SP4 versus SP3a Grunners SQL Server 3 09-01-2006 06:05 AM
VB versus VBA aziz rasul Modules & VBA 25 08-22-2003 11:12 PM
Should Contain versus Must Contain Zeppelin Queries 7 02-19-2003 08:27 PM




All times are GMT -8. The time now is 06:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World