Text Box Percentage Conundrum

[Kevin Field]

Hi all,

Quick one i hope.

I have a text box in my report which provides a percentage figure of two other text boxes. The text box control is set to this:

=[text77]/[text88]

Also, the format is set to percentage.

This all works amazingly if there is data. However, if there is none it prints:

#Num!

I know this is obviously because it cant provide the figure due to lack of data.

However, i want the box report to show a - symbol or something similar if this is the case. But if the data is there i still want to show the correct percentage.

I know i need an IIf statement but i seriously cant work it out.

Any help?

Thanks!

 

[wiklendt]

have you tried nz()?

 

[vbaInet]

Try this:

=IIF(Not IsNumeric([text88]), "", IIF(Not IsNumeric(text77) OR Nz([text88],0)=0, "", Val([text77])/Val([text88])))

If the names text77 and text88 are not for the purpose of asking a question, then I would advise you give your textboxes meaningful names.

 

[SteveSchapel]

Kevin,

I believe the only way you will get #Num! displayed here is if both textboxes are zero. So if there is no data in either of them, that should be fine. Therefore, you possibly need something like this:

=IIf([text77]=0 And [text88]=0,Null,[text77]/[text88])

 

[Kevin Field]

VB

You have no idea how much of a genius you are.

Works like a charm, as does everytime you help.

THANK YOU!

 

[vbaInet]

Glad we could help.

Actually, you don't need the Val() functions.