Adding numbers in percentage format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several calculated fields on a report which, in some instances, end up
being divided by 0. I use the following in the Control Source of each of the
fields:

=IIf([text18]=0,"0.0%",[text12]/[text18])

At the end of the report, not in the page footer, I need to add all these
fields, ex. =([text22]+[text24]+[text25]+[text26]+[text27]). Usually the
totals are correct and also in percentage format. The issue is when all the
Control Source = 0, the display looks like this:

0.0%0.0%0.0%0.0%0.0%

Is Access looking at the fields as text fields and just putting them
together? How can I get the field to display 0.0%?

Thank you.
 
Yes. "0.0%" is a string so Access concatenates the strings when you use the
+ operator instead of adding them. You need to apply the format to the
control rather than creating a string as a result of your IIf()

=IIf([text18]=0,0,[text12]/[text18])

Set the format property of the control to show it as a percent.

BTW - text12 and text18 have no meaning. The best thing to do when building
forms/reports is to rename each control to something meaningful. That way
your calculations will make sense. If you change them now, you'll have a
lot of manual work to do since Access will not propagate the changes but I
would recommend it anyway if this is an application you are building for
someone else.

=IIf([QuantityOrdered]=0,"0.0%",[SumOfPrice]/[QuantityOrdered])
 
Thank you! It is working now. And thank you for the help renaming the fields
also.

Pat Hartman (MVP) said:
Yes. "0.0%" is a string so Access concatenates the strings when you use the
+ operator instead of adding them. You need to apply the format to the
control rather than creating a string as a result of your IIf()

=IIf([text18]=0,0,[text12]/[text18])

Set the format property of the control to show it as a percent.

BTW - text12 and text18 have no meaning. The best thing to do when building
forms/reports is to rename each control to something meaningful. That way
your calculations will make sense. If you change them now, you'll have a
lot of manual work to do since Access will not propagate the changes but I
would recommend it anyway if this is an application you are building for
someone else.

=IIf([QuantityOrdered]=0,"0.0%",[SumOfPrice]/[QuantityOrdered])

Danu said:
I have several calculated fields on a report which, in some instances, end
up
being divided by 0. I use the following in the Control Source of each of
the
fields:

=IIf([text18]=0,"0.0%",[text12]/[text18])

At the end of the report, not in the page footer, I need to add all these
fields, ex. =([text22]+[text24]+[text25]+[text26]+[text27]). Usually the
totals are correct and also in percentage format. The issue is when all
the
Control Source = 0, the display looks like this:

0.0%0.0%0.0%0.0%0.0%

Is Access looking at the fields as text fields and just putting them
together? How can I get the field to display 0.0%?

Thank you.
Click to expand...
Click to expand...
 
Back
Top