Formatting Formula Result

  • Thread starter Thread starter Kitty
  • Start date Start date
K

Kitty

I'm working on a report where the user wants the results
of certain calculations to appear in brackets to set them
off from the other numbers on the report.

The Format property is set to standard with 0 decimal
places for these fields.

The control source for one text box is:

=IIf(([Coetot$]>0),"[ " & (([CoeTOT$])/([CTOTALS])*100)
& " ]",0)

The brackets show. However, the result is not multiplied
by 100 and all decimal places appear. I've tried putting
parentheses outside the brackets and get the same
results. Without the brackets in the formula, I get the
whole number I want.

Thanks for any suggestions.

Kitty
 
I see no reason why the correct value would not be calculated. You should
recheck that.
As for the formatting, by adding the brackets, you are converting the result
to a string. The format property of the control will not apply to a string.
You need to include the desired format in the formula.

For instance:
=IIf(([Coetot$]>0),"[ " & Format((([CoeTOT$])/([CTOTALS])*100) ,"0") &
" ]",0)
 
I'm working on a report where the user wants the results
of certain calculations to appear in brackets to set them
off from the other numbers on the report.

The Format property is set to standard with 0 decimal
places for these fields.

The control source for one text box is:

=IIf(([Coetot$]>0),"[ " & (([CoeTOT$])/([CTOTALS])*100)
& " ]",0)

The brackets show. However, the result is not multiplied
by 100 and all decimal places appear. I've tried putting
parentheses outside the brackets and get the same
results. Without the brackets in the formula, I get the
whole number I want.

Thanks for any suggestions.

Kitty
Click to expand...

You haven't restricted any decimals so they should all appear.
Try it without all the extra unnecessary parentheses and see what you
get.

=IIf([Coetot$]>0,"[ " & [CoeTOT$]/[CTOTALS]*100 & " ]",0)

If you also wish to limit decimals (let's say to 2), then:
=IIf([Coetot$]>0,"[ " & Format([CoeTOT$]/[CTOTALS]*100,"#,##0.00") & "
]",0)

Or ... you can also simply include the brackets within the Format
function:

=IIf([Coetot$]>0, Format([CoeTOT$]/[CTOTALS]*100,"[ #,##0.00 ]"),0)
 
Thank you! Works perfectly.
-----Original Message-----
I see no reason why the correct value would not be calculated. You should
recheck that.
As for the formatting, by adding the brackets, you are converting the result
to a string. The format property of the control will not apply to a string.
You need to include the desired format in the formula.

For instance:
=IIf(([Coetot$]>0),"[ " & Format((([CoeTOT$])/([CTOTALS]) *100) ,"0") &
" ]",0)

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I'm working on a report where the user wants the results
of certain calculations to appear in brackets to set them
off from the other numbers on the report.

The Format property is set to standard with 0 decimal
places for these fields.

The control source for one text box is:

=IIf(([Coetot$]>0),"[ " & (([CoeTOT$])/([CTOTALS])*100)
& " ]",0)

The brackets show. However, the result is not multiplied
by 100 and all decimal places appear. I've tried putting
parentheses outside the brackets and get the same
results. Without the brackets in the formula, I get the
whole number I want.

Thanks for any suggestions.

Kitty
Click to expand...


.
Click to expand...
 
Back
Top