Solved

MS Access Query Sum Total IIF criteria IsNull Help Please

Posted on 2016-11-28
7
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 20

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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