Report field conditional format (1 Viewer)

M

MartinO

Registered User.

Local time

Today, 14:49

Joined

Oct 8, 2002

Messages

22

• May 11, 2004

• #1

I'm trying to put a conditional format on a report field using vba in access 97 with little success.

Basically there are two date fields [Date Forecast] and [Date Actual].
If [Date Actual] exceeds [Date Forecast]then the background colour of the field needs to turn red and the text white.

The code I thought would work ...

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

If [Date Actual] > [Date Forecast] Then
BackColor = 255
ForeColor = 16777215
End If
End Sub

All this gives me is a 'variable not defined' error

Anyone any ideas?[/FONT]

 

Treason

#@$%#!

Local time

Today, 09:49

Joined

Mar 12, 2002

Messages

340

• May 11, 2004

• #2

Conditional Formatting is not available in Access 97. The feature is only available in Access 2000 or better...

 

M

MartinO

Registered User.

Local time

Today, 14:49

Joined

Oct 8, 2002

Messages

22

• May 11, 2004

• #3

Treason said:

Conditional Formatting is not available in Access 97. The feature is only available in Access 2000 or better...

Click to expand...

Already knowing that I was trying to see if there was a way round it using vba. The closest I've come is to highlight the whole record line rather than the individual fields using...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim pod As Date
Dim dop As Date
cd = cd + 1
pod = [Date A].OldValue
dop = [Date B].OldValue
If pod > dop Then
Detail.BackColor = 65280
Else
Detail.BackColor = 16777215
End If
End Sub

This gives a "subtle" background colour of Flourescent green where the condition exists (Useful when printing on a b&w printer).


but if anyone knows another way.........

 

R

Rich

Guest

• May 11, 2004

• #4

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

If Me.MyContr1 > Me.MyContr2 Then
Me.MyContr1.BackColor = 255
Me.MyContr1.ForeColor = 16777215
Else
Me.MyContr1.BackColor = something else
Me.MyContr1.ForeColor = something else

End If
End Sub

 

M

MartinO

Registered User.

Local time

Today, 14:49

Joined

Oct 8, 2002

Messages

22

• May 11, 2004

• #5

Rich said:

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

If Me.MyContr1 > Me.MyContr2 Then
Me.MyContr1.BackColor = 255
Me.MyContr1.ForeColor = 16777215
Else
Me.MyContr1.BackColor = something else
Me.MyContr1.ForeColor = something else

End If
End Sub

Click to expand...

Unfortunateley 'Backcolor' and 'Forecolor' do not seem to be options in access 97
using this code the fields appear blank

 

R

Rich

Guest

• May 11, 2004

• #6

They are available options in 97, make sure the backround is not set to transparent

 

M

MartinO

Registered User.

Local time

Today, 14:49

Joined

Oct 8, 2002

Messages

22

• May 11, 2004

• #7

Rich said:

They are available options in 97, make sure the backround is not set to transparent

Click to expand...

Spot on!
Thanks for that - I now have two different ways to highlight.