Conditional formatting will not work with Now()

[Jason]

I am stumped on this one!!

I am trying to format data on a report, so that if the end date for the
record, is less than the current date, then the entire record will be red.

Hee is the code I am using:

Private Sub Report_Page()


Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If Format([TXT_ENDDATE], "\#dd\/mm\/yyyy\#") <= Format(Now()dd\/mm\/yyyy\#")
Then
[TXT_ENDDATE].ForeColor = lngRed
Else
[TXT_ENDDATE].ForeColor = lngBlack
End If
End Sub

It simply will not format the text. Yes, conditional formatting is an
option, but this is limited to just one text box, and not all fields in the
record.

FYI.... The [TXT_ENDDATE] is a date field, with formatting set to dd/mm/yyyy

Any ideas ?

 

[Marshall Barton]

I am trying to format data on a report, so that if the end date for the
record, is less than the current date, then the entire record will be red.

Hee is the code I am using:

Private Sub Report_Page()

Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If Format([TXT_ENDDATE], "\#dd\/mm\/yyyy\#") <= Format(Now()dd\/mm\/yyyy\#")
Then
[TXT_ENDDATE].ForeColor = lngRed
Else
[TXT_ENDDATE].ForeColor = lngBlack
End If
End Sub

It simply will not format the text. Yes, conditional formatting is an
option, but this is limited to just one text box, and not all fields in the
record.

FYI.... The [TXT_ENDDATE] is a date field, with formatting set to dd/mm/yyyy

First, when you are comparing dates, compare the date
values. not a formatted version of the dates.

Second, the Page event can not be used to manipulate a
control's properties (there may be many copies of a control
on a page.) You need to use the Format event of the section
(detail?) that contain the controls.

Minor point, the basic colors you are using already have
built in declarations (vbRed, vbWhite, etc)

I think your xode should be more like:

Private Sub Report_Format(...
Dim lngColor As Long

If Me.TXT_ENDDATE < Date Then
lngColor = vbRed
Else
lngColor = vbBlack
End If

Me.TXT_ENDDATE.ForeColor = lngColor
Me.thistextbox.ForeColor = lngColor
Me.thattextbox.ForeColor = lngColor
. . .
End Sub

 

[KARL DEWEY]

If it is a datetime field the format does not matter when comparing a
datetime to it.

Try this --
If [TXT_ENDDATE] <= Now()

 

[Jason]

That did it...... Thank you so very much for your kind assistance and tutorial.

I am trying to format data on a report, so that if the end date for the
record, is less than the current date, then the entire record will be red.

Hee is the code I am using:

Private Sub Report_Page()

Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If Format([TXT_ENDDATE], "\#dd\/mm\/yyyy\#") <= Format(Now()dd\/mm\/yyyy\#")
Then
[TXT_ENDDATE].ForeColor = lngRed
Else
[TXT_ENDDATE].ForeColor = lngBlack
End If
End Sub

It simply will not format the text. Yes, conditional formatting is an
option, but this is limited to just one text box, and not all fields in the
record.

FYI.... The [TXT_ENDDATE] is a date field, with formatting set to dd/mm/yyyy

First, when you are comparing dates, compare the date
values. not a formatted version of the dates.

Second, the Page event can not be used to manipulate a
control's properties (there may be many copies of a control
on a page.) You need to use the Format event of the section
(detail?) that contain the controls.

Minor point, the basic colors you are using already have
built in declarations (vbRed, vbWhite, etc)

I think your xode should be more like:

Private Sub Report_Format(...
Dim lngColor As Long

If Me.TXT_ENDDATE < Date Then
lngColor = vbRed
Else
lngColor = vbBlack
End If

Me.TXT_ENDDATE.ForeColor = lngColor
Me.thistextbox.ForeColor = lngColor
Me.thattextbox.ForeColor = lngColor
. . .
End Sub