Forcing £0.00 on a NULL value

[saross]

Hi all. Still struggling with this...

I've used 0;0;0;0 on the Format of the text boxes in the report to force a 0 where NULL is present, but how do I change this so it forces a £0.00 instead? The problem is that in forcing a 0 I've lost my original formatting where there is a positive currency value so it no longer displays the £ nor the decimal places.

Can anyone please help?

 

[Mile-O]

Since a NULL is an unknown value there is no way the format function knows which of the formatting options to apply to the NULL.

So, you'll need to go to the query bound to the report and add a calculated field in place of the one you are currently using for this formatting operation.

Rather than just the field name, you'll need to use the following expression to create the calculated field:

NewField: IIf(IsNull([MyField]), 0, [MyField])​

You can now format this.

 

[Rich]

Set the Format for the control to Currency and use the Nz function
=Nz([YourField],0)

 

[Mile-O]

I've been using Excel too much lately - I forgot about the Nz function.

 

[Rich]

It's been so long since I used Excel that I've forgotten how it works