NZ function (1 Viewer)

[swarm6]

I have a report that is connected to the query (below). I want a number to show on the report, but if it is null it is blank, so I want a null to show as 0. Here is my SQL:

SELECT Nz([CountOfStatID],0)+0 AS NP, IIf(IsNull([qryFPCNNewParentSub]![CountOfStatID]),0,[qryFPCNNewParentSub]![CountOfStatID]) AS Expr1
FROM qryFPCNNewParentSub;

It looks convoluted because I was trying to trick it. Can someone help me make the null show as a 0?

The CountOfStatId comes from a sub query. I only need one column to show.

Any help would be appreciated! Thank you!

 

[Mihail]

The easier way to achieve this is to create one more field in your query:
NewField: NZ([OriginalField],[OriginalField],0)
Then set the control source for the control to this field.

 

[swarm6]

Thank you, I tried it, but I still get null. (I left out one of the original fields, because it said wrong number of arguments.)

 

[Mihail]

I left out one of the original fields, because it said wrong number of arguments

Correct. My bad. This is the syntax for the IIF statement.

Can you upload the DB with some data to test ?
Convert to Access 2003 version if you can (I use 2007) then ZIP it.

 

[swarm6]

Hmmm. Can we see if we can resolve without me uploading my database? Here is the new SQL with your suggestion.

SELECT Nz([CountOfStatID],0) AS Expr2
FROM qryFPCNNewParentSub;

What am I doing wrong? Thanks for your help!!

 

[Mihail]

SQL is far away from my best point but, from what I see, all seems to be OK (as SQL).

On the other hand, something don't sound well: CountOfStatID
ID sound as IDentificator (Prymary Key) that can't be null
Count, as well, can't be null. Can be ZERO, but not null.

Are you (very) sure that in the field CountOfStatID you have NULL values ?
I suspect that you have either zero length strings either strings that contain one or more spaces (and only spaces).

 

[swarm6]

You are so kind to help. I believe it is a null because no records meet the criteria I gave it in the subquery. SQL is not my strong point either. Here it is in the build window:

Expr2: Nz([CountOfStatID],0)

 

[Mihail]

Sorry but I have no more ideas.
I advice you to post also the SQL for the subquery.
Not for me, but there are here true SQL gurus that can read (and understand ) it.

 

[swarm6]

Great idea - here is the subquery:

SELECT Count(tblStatistics.StatID) AS CountOfStatID
FROM tblStatistics
WHERE (((tblStatistics.VisitDate) Between [Forms]![frmOpenMonthMonitorRpt]![txtBeginDate] And [Forms]![frmOpenMonthMonitorRpt]![txtEndDate]) AND ((tblStatistics.SiteID)=[Forms]![frmOpenMonthMonitorRpt]![comboSite]))
GROUP BY tblStatistics.PregFPCN, tblStatistics.NewClients
HAVING (((tblStatistics.PregFPCN)=4) AND ((tblStatistics.NewClients)=Yes));

Thank you again.