"IIf" statements possible in a report text box?

[Guest]

I have a report text box that calculates the division of the values in two
fields. Unfortunately, on very rare occasions, the field used for the
denominator is zero, which of course results in divide by zero errors.

It would be very difficult to add something to the underlying query for this
report, due to its tricky construction from other links. Ideally, I'd like a
calculated control within the report itself which would test to see if the
denominator field was zero. If so, the calculation would be skipped, and the
text box would just be blank. If it were any number other than zero, the
division would occur, and the text box would display the value.

I thought that maybe I could use an IIf statement in the text box, but I
can't seem to come up with any workable syntax. I can get an IIf statement to
do the right thing in a query, but as I said, the particular query for this
report would be a nightmare to tweak.

Anyone have ideas?

Thanks,
Wes

 

[Jeff L]

In your box on your report you would put:
=IIF([YourValue2] = 0, "", [YourValue1]/[YourValue2])

Hope that helps!

 

[Guest]

Jeff,

Thanks for the help. That looked the same as what I had before, but worked
just fine.

So I thought, if your suggested statement and mine were the same, why would
only one work? Then I went back and looked at my original test statement, and
eventually realized that my problem all along had been that I built my
statement in a text editor, and my "" were not being interpreted in Access as
" marks. Once I replaced them using the "build" dialog, everything worked
fine.

Here's my final statement:
=IIf((Sum([Math_Performance_Level]<5)=0),"",(Sum(([Math_Performance_Level]>2) And ([Math_Performance_Level]<5)))/(Sum([Math_Performance_Level]<5)))

Thank Again!
Wes

In your box on your report you would put:
=IIF([YourValue2] = 0, "", [YourValue1]/[YourValue2])

Hope that helps!

I have a report text box that calculates the division of the values in two
fields. Unfortunately, on very rare occasions, the field used for the
denominator is zero, which of course results in divide by zero errors.

It would be very difficult to add something to the underlying query for this
report, due to its tricky construction from other links. Ideally, I'd like a
calculated control within the report itself which would test to see if the
denominator field was zero. If so, the calculation would be skipped, and the
text box would just be blank. If it were any number other than zero, the
division would occur, and the text box would display the value.

I thought that maybe I could use an IIf statement in the text box, but I
can't seem to come up with any workable syntax. I can get an IIf statement to
do the right thing in a query, but as I said, the particular query for this
report would be a nightmare to tweak.

Anyone have ideas?

Thanks,
Wes

 

[Guest]

I have a report where I can't avoid divding by zero and your IIF statement
worked like a champ. Thanks for help.

Jeff,

Thanks for the help. That looked the same as what I had before, but worked
just fine.

So I thought, if your suggested statement and mine were the same, why would
only one work? Then I went back and looked at my original test statement, and
eventually realized that my problem all along had been that I built my
statement in a text editor, and my "" were not being interpreted in Access as
" marks. Once I replaced them using the "build" dialog, everything worked
fine.

Here's my final statement:
=IIf((Sum([Math_Performance_Level]<5)=0),"",(Sum(([Math_Performance_Level]>2) And ([Math_Performance_Level]<5)))/(Sum([Math_Performance_Level]<5)))

Thank Again!
Wes

In your box on your report you would put:
=IIF([YourValue2] = 0, "", [YourValue1]/[YourValue2])

Hope that helps!

I have a report text box that calculates the division of the values in two
fields. Unfortunately, on very rare occasions, the field used for the
denominator is zero, which of course results in divide by zero errors.

It would be very difficult to add something to the underlying query for this
report, due to its tricky construction from other links. Ideally, I'd like a
calculated control within the report itself which would test to see if the
denominator field was zero. If so, the calculation would be skipped, and the
text box would just be blank. If it were any number other than zero, the
division would occur, and the text box would display the value.

I thought that maybe I could use an IIf statement in the text box, but I
can't seem to come up with any workable syntax. I can get an IIf statement to
do the right thing in a query, but as I said, the particular query for this
report would be a nightmare to tweak.

Anyone have ideas?

Thanks,
Wes