Conditional Formatting Problem - VBA 1

[JStrand]

I have a report with Field Name = [WO_GrossRev_Var1] which is in the Detail Section of the report. The Datasouce contains a hundred records or so. I want each record on the report to have a BackColor of Yellow (RGB(255, 255, 0))if the value is <-0.0050 else black. On the On Format Event of the report detail, I've added the following procedure which produces no conditional formatting effect:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim MyWOGrossRevVar As Double
MyWOGrossRevVar = [WO_GrossRev_Var1]
If MyWOGrossRevVar < -0.005 Then
[WO_GrossRev_Var1].BackColor = RGB(255, 255, 0)
Else
[WO_GrossRev_Var1].BackColor = RGB(255, 255, 255)
End If

End Sub


Can anyone see the problem with this code? Thank you in advance.

 

[MajP]

That is not really conditional formatting, and if you go that approach I think it can only be viewed in Print Preview not Report View. Ensure you are in print preview.

I would use real conditional formatting.

http://office.microsoft.com/en-us/a...using-conditional-formatting-HA010208133.aspx

 

[JStrand]

Thanks MajP. I used your suggested method first and strangely it will apply a bold type face when the condition is met, but won't apply a backcolor of yellow. Therefore I tried the VBA method on the report detail section. There must be some other backcolor property overriding the conditional format I'm attempting to apply.

 

[dhookom]

I expect you have the background set to transparent.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Backstyle = transparent
backcolor = #FFFFFF

 

[JStrand]

Changing Backstyle = normal does the trick. Thanks Duane.

 

[JStrand]

I have an additional condition format to be applied to the problem above which I need some help with. The test above was for a format to be applied (backcolor = yellow)if a condition was met for a specific period. Now I need an additional format to be applied (backcolor = red) if the same condition is met in two successive periods, or yellow if it is the first occurrence.

I went back to the VBA method and it works fine for the first period test but I need some help on the SQL required for the additional test. I've reduced the SQL below for just the first field which is tested. I receive an error message upon execution: Object doesn't support this property or method. I'm pretty sure I'm mis-using the If / And method. If you have any suggestions I would appreciate it.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyWOGrossRevVar As Double
Dim MyWOGrossRevPP As Double

MyWOGrossRevVar = WO_GrossRev_Var1
MyWOGrossRevPP = WO_GrossRev_PP

If MyWOGrossRevVar < -0.05 And MyWOGrossRevPP < -0.05 Then
WO_GrossRev_Var1.BackColor = RGB(255, 125, 125)
Else IF MyWOGrossRevVar < -0.05 Then
WO_GrossRev_Var1.BackColor = RGB(255, 255, 0)
Else
WO_GrossRev_Var1.BackColor = RGB(255, 255, 255)
End If

End Sub

 

[JStrand]

I was missing a name on my control source and this SQL appears to work for the second condition. If you see any other problems please advise. Thank you..

 

[dhookom]

Your code is confusing (at least to me) when you don't use [red]Me.[/red] when referencing controls on your report.

Duane
Hook'D on Access
MS Access MVP