Query Formula with formatting

[LB79]

Hello,

I've tried a few ways of trying to achieve this but I keep getting errors.
I have a query field that is a null value. I want to add a formula to the header so that if the result is null it shows as 0.
Thing is I also want formatting in place.
This is what I have so far:

IIf([Target Amount]="",0,Format(Sum([Target Amount]),"#,##"))

If I remove the format bit it works and vice versa… just not together.

How can I tweek it to stop the error?

Thanks

 

[Soton]

If the result is sent to a text box on a Form, you can always use the formatting options on the text box. Other destinations also have their own formatting options. It depends what you want to do with the result.

 

[vbaInet]

To expand on Soton said:

1. You test for the condition first (as the Control Source for example) without Format
=IIf([Target Amount]="",0,[Target Amount])
2. Then apply the format on the Format property of your text box.
#,##