No Data Available instead of Null

[Ice Rhino]

I have a report which is made up of a complex(ish) Stored Procedure within SQL 2K. This involves a number of Cross Joins, Left Joins etc etc. There are various tables quizzed by the SP and sometimes there is data to return and sometimes not.

On the report, obviously the control source is set to that of the result set that the SP generates. Is it possible to make the output of the field to display 'No Data Available' if the result is Null rather than the field just being blank?

I have posted this is reports as apose to SQL server folder as it seems more of a reports function rather than SQL specific.

Regards

 

[Newman]

=Nz([YourField],"No Data Available")

 

[Ice Rhino]

Thanks for the response, when I put that line in, I get an error. The helpful tips say that it is a circular reference.

The line I used was

=Nz([UnderOffer],"No Data Available"), UnderOffer being the original value in Control Source prior to your suggestion.

What does the =NZ bit do, is it a typo or have I missed something here?

Regards

 

[Ice Rhino]

OK, I have now found what NZ is, but this appears to be a function that is used in a VB environment. Is there an equivelant version that I can use in the report?

Regards

 

[Ice Rhino]

Is there a way to hack IIf(expr, truepart, falsepart) to work in this situation.

I have tried

IIf([IsNull], "No Data Available", [UnderOffer])

But that just seems to upset it and does not work, so I am guessing the format is wrong

Regards

 

[Rich]

Are you talking about records with Null values or No Records at all?

Iif(IsNull([SomeField]),"SomethingElse",[SomeField])

and the Nz function isn't exclusive to vba, you can use it throughout Access

 

[Ice Rhino]

Hi

I found the problem with my IIF statement, there was a circular reference because the object name was the same as the control source name.

Bad naming convention on my part. All is well now with what I used

=IIf(IsNull([UnderOffer]),0,[UnderOffer])

Changed the No Data Available to zero as it is more descriptive

Regards and thanks for the help and suggestions