if null then zero

[mlrucci]

Good afternoon, I am attempting to create a report based on data input. It is important to show 0 if no results are found. I have a query for one of the subreports that could potentially be null and need it to equal 0 if is null to ensure accuracy and better visualization of possible missing information for the end user. I have attempted nz function, dcount and if isnull. After hours of trying different combinations, I have failed. I have attempted to place in criteria and create an expression as well as build into the report via vba to no avail. Can anyone help. Here is the query. Any help would be appreciated. Thank you



SELECT tblAppImportFM_LM.SOWID, qryFM_IPS_LM_Price.ProductID, tblPackage.PackageColor, Sum(tblAppImportFM_LM.Imprints) AS CountColor, qryFM_IPS_LM_Price.ProductPrice, [CountColor]*[ProductPrice] AS TotalColor, qryFM_IPS_LM_Price.PricingActive
FROM tblPackage INNER JOIN (tblAppImportFM_LM INNER JOIN qryFM_IPS_LM_Price ON tblAppImportFM_LM.SOWID = qryFM_IPS_LM_Price.SOWID) ON (tblPackage.PackageNumberID = tblAppImportFM_LM.PackageNumberID) AND (tblPackage.SOWID = tblAppImportFM_LM.SOWID)
GROUP BY tblAppImportFM_LM.SOWID, qryFM_IPS_LM_Price.ProductID, tblPackage.PackageColor, qryFM_IPS_LM_Price.ProductPrice, qryFM_IPS_LM_Price.PricingActive
HAVING (((qryFM_IPS_LM_Price.ProductID)=19) AND ((tblPackage.PackageColor)=Yes) AND ((qryFM_IPS_LM_Price.PricingActive)=Yes));

[orange]

Review this function NZ

[mlrucci]

I have attempted to use this multiple times. Maybe I am not putting it in the right area. attempted to place in the criteria and as an expression and it has failed. I have simplified the qry to just show the null variant as 0. Not sure what the problem is.

SELECT qryCalcFM_IPS_LM_Color.CountColor, Nz([CountColor],0) AS Expr1
FROM qryCalcFM_IPS_LM_Color;

[Micron]

I see no evidence in that sql of where any of those things were applied and no mention of that field name, so totally clueless as to which field the desired 0 is for. If it is for one of the equal joined fields it will likely never produce the required results. "Didn't work" doesn't help - post what you tried?

[orange]

Did you look at the Query example?

it has failed

What error/result is given?

[Micron]

IMO, might be best to convert all nulls and/or zls to zero with update query, then set default at table field level going forward. If 0 is preferred in the report, would it not make sense that it's equally important in the data?

[mlrucci]

First, I would like to say think you all for your help. I am novice and self taught, so there are somethings I am missing in my knowledge of db development. I have included a very stripped down version of my db. I am attempting to show 0 if is null in the qry qryCalcFM_IPS_LM_Color. Database1_Zero.zip

[Welshgasman]

Well from this
Sum(tblAppImportFM_LM.Imprints) AS CountColor

you need to use the NZ() on the Imprints field?

[mlrucci]

I have created a query straight from the table to see if there was a problem with my qry and continue to still have the problem.

SELECT tblAppImportFM_LM.Imprints, tblPackage.PackageColor
FROM tblAppImportFM_LM INNER JOIN tblPackage ON tblAppImportFM_LM.PackageNumberID = tblPackage.PackageNumberID
GROUP BY tblAppImportFM_LM.Imprints, tblPackage.PackageColor
HAVING (((tblAppImportFM_LM.Imprints)=Nz([Imprints],0)) AND ((tblPackage.PackageColor)=Yes));

Tried to use it as an expression:
SELECT tblAppImportFM_LM.Imprints, tblPackage.PackageColor, Nz([Imprints],0) AS Expr1
FROM tblAppImportFM_LM INNER JOIN tblPackage ON tblAppImportFM_LM.PackageNumberID = tblPackage.PackageNumberID
GROUP BY tblAppImportFM_LM.Imprints, tblPackage.PackageColor, Nz([Imprints],0)
HAVING (((tblPackage.PackageColor)=Yes));

but need to use the Imprints as sum. This throws the error "Your query does not include the specified expression "Sum(tblAppImportFM_LM.Imprints=nz(Imprints],0) and the tble package.packageColor =-1 as part of an aggregate function. I am under the impression that you cannot use the nz if it used as a sum and not groupby. stumped and frustrated.

[Welshgasman]

I was thinking more along the lines of

Sum(NZ(tblAppImportFM_LM.Imprints,0)) AS CountColor

[Micron]

Again, if you coerce a null to 0 in a calculated query field, that means the table field is really null thus those records don't have zeros in them. How can we expect
HAVING (((tblAppImportFM_LM.Imprints)=Nz([Imprints],0)) to work where [Imprints] is null? NZ will make the expression result in

HAVING (((tblAppImportFM_LM.Imprints)= 0)) and there are no zeros? Or am I missing something?
Also we have "SELECT ... 0 AS Expr1..." for where Imprints is null.
Have to go for now, will check back later. I finally decided to download db and see that some field have table aliases. Can't recall if that will cause issues on calculated fields when you use the alias name as I haven't used alias field names in tables since my dinosaur died.

[Welshgasman]

I corrected to this, and then did not get any data.?

SELECT tblAppImportFM_LM.SOWID, qryFM_IPS_LM_Price.ProductID, tblPackage.PackageColor, Sum(NZ([Imprints],0)) AS CountColor, qryFM_IPS_LM_Price.ProductPrice, Sum(NZ([Imprints],0)*[ProductPrice]) AS TotalColor, qryFM_IPS_LM_Price.PricingActive
FROM tblPackage INNER JOIN (tblAppImportFM_LM INNER JOIN qryFM_IPS_LM_Price ON tblAppImportFM_LM.SOWID = qryFM_IPS_LM_Price.SOWID) ON (tblPackage.PackageNumberID = tblAppImportFM_LM.PackageNumberID) AND (tblPackage.SOWID = tblAppImportFM_LM.SOWID)
GROUP BY tblAppImportFM_LM.SOWID, qryFM_IPS_LM_Price.ProductID, tblPackage.PackageColor, qryFM_IPS_LM_Price.ProductPrice, qryFM_IPS_LM_Price.PricingActive
HAVING (((tblPackage.PackageColor)=Yes) AND ((qryFM_IPS_LM_Price.PricingActive)=Yes));

and then amended the PackageColour to Yes to at least get something in the query.

Start of small.

Get the data row by row correct first, THEN attempt to group and Sum.?

You need to be able to see if those figures are actually correct?, si the data correct etc?

[mlrucci]

That helped. figured it out! Thank you very much.