Highlighting Percent Changes

[Therat]

I have a huge report that calculates the percent change over the previous quarters in the detail and footer sections of my report. My VP wants to scan the 20+ page report and easily find significant changes.

Question: Is there a way to highlight the changes that are >5% or <5% with a different color. Or even a different format?

Thanks in advance!

 

[Elana]

Try this:

On the OnFormat event of the section, place this code:

Select Case me.yourpercentfield

Case > .05

Me.YourPercentField > .05 Then
Me.YourPercentField.FontBold = True
Me.YourPercentField.FontSize = 10
Me.YourPercentField.FontName = "Copperplate Gothic Bold"
Me.YourPercentField.BackColor = 12632256

Case else
'don't do anything, print with normalreport font.
End select

You will probably have to tinker with the comparison value...depending on how your field is storing that number (.05 might not be correct). Plus, all the properties I specified can be modified to whatever you want.

HTH

 

[Therat]

Changing the format code

I can't quite get it to work. It is getting stuck at the line

Me.PQR > 0.05 Then


The ">" sign seems to be a problem.

P.S. I don't know if it matters, but I have Access 97. (maybe someday my organization might update our software)

Thanks!

 

[Elana]

Sorry,

Take the "> .05" out of the me.yourpctfield > .05 line. I should have taken that out when I submitted my post.

Try again - it should work, but let me know if it doesn't.

E

 

[Therat]

Not quite there!

I'm not sure of your last advice.

Here's what I got so far...

Private Sub Report_Open(Cancel As Integer)


Select Case Me.PQR

Case Is > 0.05

Me.PQR Then
Me.PQR.FontBold = True
Me.PQR.FontSize = 10
Me.PQR.FontName = "Copperplate Gothic Bold"
Me.PQR.BackColor = 12632256

Case Else
'don't do anything, print with normalreport font.
End Select


End Sub

 

[Elana]

Delete the line

"Me.PQR Then"

We are using Select Case instead of If/Then, so no need to use "then." Sorry for the confusion - I typed my post too quickly and made some mistakes there.


E