Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

MS Access Query Sum Total IIF criteria IsNull Help Please

I have a query where I have several Product Locations and I have Product Quantity Count

I am trying to get a total count of a product when it is found in multiple locations. I have had success with that part.

But if there is 0 product available it shows in the query blank but I want it to show 0 as in ZERO!

Here is my SQL for the original Query:
SELECT Products.ProductID, Sum(ProdLocations.QtyLoc) AS SumOfQtyLoc
FROM Products LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
GROUP BY Products.ProductID;

Open in new window


and here is what I tried puting in the criteria for QtyLoc.ProdLocations Sum. This one says wrong number of Arguments:
IIf(IsNull([SumOfQtyLoc],"0", [SumOfQtyLoc])

Open in new window


and this one says Data Type Mismatch:
IIf(IsNull("SumOfQtyLoc"),"0","SumOfQtyLoc")

Open in new window


ProductID = Integer
SumOfQtyLoc = Integer
QtyLoc = Integer

Thanks!
0
Dustin Stanley
Asked:
Dustin Stanley
3 Solutions
 
Patrick MatthewsCommented:
SELECT Products.ProductID, Sum(Nz(ProdLocations.QtyLoc, 0)) AS SumOfQtyLoc
FROM Products LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
GROUP BY Products.ProductID;

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
zero is a number -- don't use delimiters

IIf(IsNull([SumOfQtyLoc]), 0, [SumOfQtyLoc])

or try this in the original Query:
Sum( iif (isNull( ProdLocations.ProductID), 0,  nz(ProdLocations.QtyLoc,0) ) ) AS SumOfQtyLoc
0
 
Rey Obrero (Capricorn1)Commented:
try this query
SELECT Products.ProductID, Sum(Nz(ProdLocations.QtyLoc,0)) AS SumOfQtyLoc
FROM Products LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
GROUP BY Products.ProductID;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
btw:
>"ProductID = Integer"

ProductID should be a Long Integer ... otherwise you will be limited to 32K
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Alright Patrick Mathews that hit the spot. Thanks Rey and Crystal also for the help.  And Crystal ProductID is a Long Integer sorry.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks!
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

btw, what Patrick gave you will work as long as every ProductID is in the ProdLocations table.  If there may be even one that is not, however, you should use what I gave you for the original Query

(hi Patrick ! missing you at the Summits. You're pretty good with Access ... don't say that about too many Excel experts ... and Rey, missing you too!)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now