Conditional Formatting Formula Expression 1

[awl]

I am looking for an appropriate formula in my Report to have the field [PRptDays] which is “a # of days”, displayed in Red if the field [PRptDays] has expired from the [DateOfExam] field. For example: The [DateOfExam] was 10/30/03 and the individual had 90 days [PRptDays] to gather his/her information together. If I open the report anytime within the 90 days, the number 90 will be displayed in black. However, if I open the report on 1 February 2004 or later, I want the # 90 displayed in red. I know how to format for the color red, it is just the expression. Thanks for any information.

 

[CosmoKramer]

Put code like this in the detail section's On Format event:

If (Date - [DateOfExam] > 90) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If

Hoc nomen meum verum non est.

 

[awl]

Thank you CosmoKramer. Most of the them work however, I have 3 instances where the color is black. I neglected to mention that the [PRptDays] could vary, but not exceed 90 days.

[DateOfExam] = 4/29/04 and [PRptDays] = 15, Expiration date should be 5/13/04
[DateOfExam] = 6/15/04 and [PRptDays] = 14, Expiration date should be 6/29/04
[DateOfExam] = 5/5/04 and [PRptDays] = 30, Expiration date should be 6/4/04

I went back to the original data to make sure the [DateOfExam] was a date field and that [PRptDays] were actual number fields.

A couple of examples that do convert to red:
[DateOfExam] = 5/5/03 and [PRptDays] = 15, Expiration date should be 5/20/03
[DateOfExam] = 2/12/04 and [PRptDays] = 15, Expiration date should be 2/27/04.

I'm not sure what the issue could be.

 

[CosmoKramer]

OK, I guess I misread the original post. So, you're using this as your statement and it is sometimes not working?:

If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If

Hoc nomen meum verum non est.

 

[awl]

Thank you CosmoKramer for your professionalism. It works great!!!! Thanks. Curtis....