Changing a font colour based on conditions

[canjfn]

We have instruments that are calibrated yearly, I have written a small database to keep the records.

On my report I have written the following to change the font colour to red if the calibration due date has been exceeded, this works fine.

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

If Me.Text42 >= Me.CalibrationDue Then
Me.CalibrationDue.ForeColor = vbRed
Else
Me.CalibrationDue.ForeColor = vbBlack
End If
End Sub

(text.42) is a hidden field on the report which will always hold the current date

What I would like to do, is that at any time I open the report if I am within 30 days of the calibration due date, then the font of the calibration due date is green, this is just to warn me that the instrument is due for calibration, hope I have explained my problem.

Thank you.

 

[pbaldy]

Unless you're using a pre-2000 version of Access, Conditional Formatting is usually easier than code. Also, you don't need to put the current date in a textbox; you can simply use Date(). You should be able to use a test like:

Between Date()-30 And Date()

either in code or in Conditional Formatting.

 

[canjfn]

Unless you're using a pre-2000 version of Access, Conditional Formatting is usually easier than code. Also, you don't need to put the current date in a textbox; you can simply use Date(). You should be able to use a test like:

Between Date()-30 And Date()

either in code or in Conditional Formatting.

Thanks for your response.

This seems to work fine

If Me.Text42 >= Me.CalibrationDue Then
Me.CalibrationDue.ForeColor = vbRed

ElseIf Me.Text42 + 30 >= Me.CalibrationDue Then
Me.CalibrationDue.ForeColor = vbGreen

Else
Me.CalibrationDue.ForeColor = vbBlack
End If