Hey everyone,

I need to display a field that does a variance calculation between 2 percentages as a whole number.

For example, Say control 1 = 55.0% and control 2 = 53.5%, the variance (in the box I am attempting to format) is 1.5%. Is there any way to custom format this variance box to show 150, instead of 1.5% ? Some of my percent variances are very small, and would like to show the basis point change, so I need to display this as the whole number. I have seen the format of #,##0, to scale down numbers, but I need to scale up this number. Any ideas?

Thank you for any help!

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Not sure I understand your situation but can't you just multiply the value by 10 or 100 (whichever is applicable)?

Just my 2 cents...

I am working in Access 2010. I am unable to multiply the value by the needed 1000, because other values (besides percentages, such as $) are displaying in the same variance control box. I currently have formatting that only applys my percentage formatting when another report value = "x". I just need to adjust this custom formatting to scale up the value (only when the other report value = x)

I hope that makes sense! Let me know if it doesn't.

Thank you.

Hi,

Thanks for the clarification but it's hard to picture what you are saying without seeing it. Are you saying that you are using Conditional Formatting? If so, can you post your expression? Thanks.

My custom formatting is being done in VBA by way of the On Format Event for the Detail section of my report. The VBA code is as follows:



This VBA is checking to see if the Account Name (on my report) = "GM %", and if it does, it applys a percentage formatting, or else it applys a scaled down number formatting. The variance (formatting that I'm trying to adjust) is in the Me.V_Prior_Q1a control. Make a little more sense?

Hi,

Thanks for the additional info. But there you go, since you have a way to evaluate the value in the control, then why not multiply it at that point. For example:

If Me.Account_Name_Formatted = "GM %" Then
Me.Current_Q1.Format = "0.0%"
Me.Prior_Q1.Format = "0.0%"

If Me.V_Prior_Q1a < 10 Then
Me.V_Prior_Q1a = Me.V_Prior_Q1a * 100
Else
Me.V_Prior_Q1a.Format = "#.00%;(#.00%)"
End If

Else
...
End If

(untested)
Just my 2 cents...