Put a blank if zero field in report??

[bsnapool]

Hi All,

Tried doing this within query, my current expression is...

Cost Difference: IIf(Len([SumOfBudget Cost£] & "")=[COLOR="Red"]0[/COLOR],[SumOfActual Cost],([SumOfActual Cost]-[SumOfBudget Cost£]))

When i replace the zero with a "-" or "blank" then it messes up the formatting of the percentage. Is there a way to do this in vba. Something like if zero then make blank??

Any advice would be appreciated.

 

[Rich]

If you want it to be Null then why put a Zero in to start with?

 

[bsnapool]

If you want it to be Null then why put a Zero in to start with?

I put a blank to start with but was messing up my formatting.

 

[Brianwarnock]

The code does not seem to tie in with the thread heading.

If a field is zeo and you want to display a blank then

IIF(fild=0," ",whatever) should work

Brian

 

[bsnapool]

I tried this, but comes up with an error#??

 

[bsnapool]

School bot error, was using the actual field!! Used a testbox and working.

 

[Rich]

I put a blank to start with but was messing up my formatting.

How did it mess up the formatting?

 

[bsnapool]

Well put the format as percentage, when added "", it would then take the percentage formatting off.

 

[rsmonkey]

depending whether this is a one time thing or abit of desing you want to implement and Update-Where statement should work fine... just whack it in your form code under a button or create a physical query in the query builder! whatever way you swing!