Solved

MS Access Query Sum Total IIF criteria IsNull Help Please

Posted on 2016-11-28
7
14 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 18

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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 18
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 18
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now