Solved

MS Access Query Sum Total IIF criteria IsNull Help Please

Posted on 2016-11-28
7
40 Views
Last Modified: 2016-11-28
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
Comment
Question by:Dustin Stanley
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 41904720
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
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41904724
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 41904727
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19
ID: 41904729
btw:
>"ProductID = Integer"

ProductID should be a Long Integer ... otherwise you will be limited to 32K
0
 

Author Comment

by:Dustin Stanley
ID: 41904761
Alright Patrick Mathews that hit the spot. Thanks Rey and Crystal also for the help.  And Crystal ProductID is a Long Integer sorry.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41904762
Thanks!
0
 
LVL 19
ID: 41904776
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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question