Am trying to write some code in Access Basic (for Access 2000) which will change forecolour of an expiry date - Red if expired, Amber if 3 months left, Green if valid. The code I have written seems to just turn the forecolour red no matter what, could somebody please help?
My code is:
If [Next_Re_Assessment] < [Now] Then
[Next_Re_Assessment].ForeColor = 255
ElseIf [Next_Re_Assessment] >= [NRAE**] And [Next_Re_Assessment] <= [Now] Then
[Next_Re_Assessment].ForeColor = 34559
Else
[Next_Re_Assessment].ForeColor = 68844
End If
**(NRAE being Next Re Assessment Date - 3 months)
1) You are using [Now] when I believe you mean Now (no brackets.. it's
a function, not a field!)
2) You should be using Date instead of Now.
Now includes a time value. Do you really want to format the control
based on whether you are running the form at 3:14:00 PM or 3:15:00 PM?
Date returns the date portion only.
If the [DateField] is earlier than today's date (whether 180 days or
89 days) your code will not even get to the first ElseIf, as the
criteria is true in both cases, so the color in either case is red.
3) Try it this way:
If [Next_Re_Assessment] >= [NRAE**] And [Next_Re_Assessment] <= Date
Then
[Next_Re_Assessment].ForeColor = 34559
ElseIf [Next_Re_Assessment] > Date Then
[Next_Re_Assessment].ForeColor = 68844
Else
[Next_Re_Assessment] = vbRed
End If
4) But then, if you're using Access 2000, why aren't you using the
control's Conditional Formatting property to do this?
Select the [Next_Re_Assessment] control, then click
Format + Conditional Formatting
Set the Condition1 Field Value Is to Between [NRAE**] and Date()
(with the parenthesis)
Set the color to Amber
[NRAE**] should in this case be a Date value such as May 15, 2004,
If it is a Number value, i.e. 90 , use Between Date() - [NRAE**] and
Date().
For Condition2 set the Field Value Is to > Date()
Set the Color to Green
No need for a Condition3.
Simply set the normal control forecolor to Red.
It's much simpler this way.
