Calculated Field and Percent Format

[Gwen H]

I have a calculated field on a report, groupPastDuePercent. The data in this
field comes from the following formula:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"Percent")

How can I either (a) force this field to display percentages with 3 or more
decimal places, or (b) display text in the field if the value is less than
0.01%? I have tried using the custom format ##.###% on the field, to no avail.

Thanks in advance,
GwenH

 

[Duane Hookom]

Don't use format() in your query. The query field should be
YourField: [groupBalancePastDue]/[groupPortfolioAmount]

Then set formatting with the Format Property of the text box in your report.

 

[Gwen H]

I'm not using Format() in my query. I'm using it in the report field itself.

And, as soon as I posted my question, I realized the answer to my own
question. In the data control source box, instead of:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"Percent")

I needed to use this:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"#0.0###%")

That worked perfectly!

Don't use format() in your query. The query field should be
YourField: [groupBalancePastDue]/[groupPortfolioAmount]

Then set formatting with the Format Property of the text box in your report.
--
Duane Hookom
MS Access MVP

I have a calculated field on a report, groupPastDuePercent. The data in
this
field comes from the following formula:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"Percent")

How can I either (a) force this field to display percentages with 3 or
more
decimal places, or (b) display text in the field if the value is less than
0.01%? I have tried using the custom format ##.###% on the field, to no
avail.

Thanks in advance,
GwenH

 

[Duane Hookom]

Your statement about data in the field confused me since the expression was
in the control source of a text box.
I would still recommend using the Format Property rather than attempting to
format in the control source.
--
Duane Hookom
MS Access MVP

I'm not using Format() in my query. I'm using it in the report field
itself.

And, as soon as I posted my question, I realized the answer to my own
question. In the data control source box, instead of:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"Percent")

I needed to use this:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"#0.0###%")

That worked perfectly!

Don't use format() in your query. The query field should be
YourField: [groupBalancePastDue]/[groupPortfolioAmount]

Then set formatting with the Format Property of the text box in your
report.
--
Duane Hookom
MS Access MVP

I have a calculated field on a report, groupPastDuePercent. The data in
this
field comes from the following formula:

Format([groupBalancePastDue]/[groupPortfolioAmount]),"Percent")

How can I either (a) force this field to display percentages with 3 or
more
decimal places, or (b) display text in the field if the value is less
than
0.01%? I have tried using the custom format ##.###% on the field, to no
avail.

Thanks in advance,
GwenH