Nz Function in SQL (1 Viewer)

[deehutchins]

Good morning,

I am using Access 2010 and I am having a slight problem with one of my reports chart. I have a record source that is feeding the chart, but my problem is that when there is no data to read it is still "showing" data set which is actually a blank row. I figure if Iuse the Nz function in the SQL it would help to read that null into zero and then my report would read better. Here is the SQL behind the chart now:

SELECT DISTINCTROW Sum(Case_review.ID) AS SumOfID
FROM Case_review
WHERE (((Case_review.[Type of Issue].Value)="Clinical"));


it would help a lot if someone could please tell me where I can place the Nz funtion in this SQL so that my null would read zero.

Thanks

 

[jdraw]

Reference to Nz()


I would try (untested)

SELECT DISTINCTROW Sum(Nz(Case_review.ID,0)) AS SumOfID
FROM Case_review
WHERE (((Case_review.[Type of Issue].Value)="Clinical"));

but I'm not sure about SUMming IDs.

Could you please tell us more about the application?

 

[deehutchins]

Basically the main report is supposed to run the charts when there is data, but the times where is no data then the report should show the labels hiding behind it but my problem is that everytime I run where I know there shouldn't be any data I get a blank square where the label should show. When I go into the record source of the report it is counting a null value as data and so that is why my chart is still showing, but it is showing blank. I was hoping that if I changed the null to zero with the Nz function I could use this code:

If Me.Rpt_Clinical.Report.HasData = 0 Then
Me.Rpt_Clinical.Visible = True
Me.Label29.Visible = False
Else
Me.Label29.Visible = True
Me.Rpt_Clinical.Visible = False
End If

and then my report would run like I want it to, but no I just tried it and it is still not working the null value is still coming up as a data record.
Please help!

Thanks,

 

[jdraw]

Did you run the sql query with the NZ? Did you get the values you expected?
Can you compare the outputs of the query with and without the NZ?
Let's focus of the query and get the right data; then move to the report.

 

[deehutchins]

Agreed, yes I did run the query in the same format in which you gave and I got the same results when I looked at it in datasheet view. It was as if the Nz function was not even working. Sorry!

 

[jdraw]

What exactly is Case_Review.ID?

Sounds like record identifier. Normally would expect a Value, or Amount etc.