Change null values to something else

[Tay]

Apologies if this has been covered before; I have searched, but not found quite what I need.

My report is based on a crosstab query. I have a couple of calculated controls which calculate percentage accuracy rates (based on the number of checks a person does against the number of errors they find in those checks). Sometimes, a person doesn't find any errors. Currently, nothing displays on the report if this is the case.

However, I've been asked if I can make '100%' appear instead of nothing (don't ask ).

I had wondered if nZ function would do it, but whatever I've tried, hasn't worked. Should it work, but I've messed it up, or do I need to do something else?

 

[MarkK]

Hey Tay, check out VBA Help for the Format property of a TextBox control, and look for sub-sections about "Number and Currency Data Types" and "Text and Memo Data Types." In these cases the Format property has multiple semi-colon delimited sections in which you can specify what the control displays for various different data values, including negative values, zero values and nulls.

This, for instance, is a valid format for a currency field...

$#,##0.00 [Black]; -$#,##0.00 [Red];"Zero";"Null"

...and does what you'd expect.
Cheers,
Mark

 

[Tay]

Thanks. I'll have a look at that when I'm back in the office on Tuesday.

p.s. did you used to have a Marvin the Martian avatar?

 

[MarkK]

Yeah, I was Marvin the Martian on here for quite a while. And you? Didn't you used to play well with others?
Cheers,
Mark