Solved Nz from subquery (1 Viewer)

[foshizzle]

This should be an easy one but my query is either showing a blank or producing an error when i try to use it for my report.

I'm trying to pull two values from an existing query, then replace any null value for AirGainLoss with a 0.

SELECT qryRGainLoss15Report.AirlineCompanyLookup, qryRGainLoss15Report.AirGainLoss, Nz([AirGainLoss]) AS AirGainLossNz
FROM qryRGainLoss15Report
GROUP BY qryRGainLoss15Report.AirlineCompanyLookup, qryRGainLoss15Report.AirGainLoss;/CODE]

What am i doing wrong?

 

[jdraw]

You need something along this.
Nz([AirGainLoss], 0)

The syntax for the Nz function in MS Access is:

Nz ( variant, [ value_if_null ] )

 

[plog]

When you use a GROUP BY, every field in the SELECT must either be in the GROUP BY or be inside an aggregate function (MIN, MAX, SUM, etc.). So that NZ field you are creating must either be placed in the GROUP BY or get a function.

However, why are you using a GROUP BY?

 

[foshizzle]

Because it was the default in option in Design View :-D haha! Still learning!

I had previously tried using Nz([AirGainLoss], 0) as jdraw suggested but I read somewhere removing the ",0" in the Nz function would still result in 0 so l just removed it during further troubleshooting. At any rate, I was able to get it working with the combined responses from both of you, as follows:

SELECT qryRGainLoss15Report.AirlineCompanyLookup, qryRGainLoss15Report.AirGainLoss, Nz([AirGainLoss],0) AS AirGainLossNz
FROM qryRGainLoss15Report;

Thanks!!!!!!