Conditional formating of date field

[Apples76]

I have several date fields on a report which are comapred against a genric
due date. i have already set up he conditional formating for when the actual
date is greater than the generic, but I also require to highlight null fileds
when the current date is greater than the generic date.

is this possible using CF in access 2K?

 

[Al Campagna]

Apples,
Conditional Formatting does not apply to reports. However, reports have their own way
to "imitate" Conditional Formatting...

Use the OnFormat event of whatever report section your controls are contained in.
For example... in the Detail section, with a Date1 field that may have Null value...
If IsNull(Date1) Then
Date1.Backcolor = QBColor(12)
End If
If Date1 could also be a date, then use additional IF statement/s to see if it's
greater than some other Date, and color it accordingly (ex. 30 days past DueDate).

In other words, the OnFormat event of reports allows you to control the appearance of
any field according to some logic (just like Conditional Formatting in forms)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Apples76]

Thanks Al,

i belive i am my way to sorting it out now.

I have come up with the following code will it work?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim datesec As String
Dim dategen As String

datesec = ("2g_daily_stats_tbl.Start on Site")
dategen = ("generic declaration dates2.Start on Site")

If IsNull(datesec) Then
If dategen >= Now() Then
datesec.BackColor = vbRed
Else
datesec.BackColor = vbWhite
End If
End If


Thanks

 

[Al Campagna]

Apples,
Without an explanation of the logic, and what the table fields represent... I can't say
for sure.

Let me use an example of a DueDate, and a PayDate.
The logic is... if PayDate is greater than DueDate then the payment is "Late", and
should be colored red.
Then....
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(PayDate) OR PayDate <= DueDate Then
PayDate.Backcolor = vbWhite
Else
PayDate.BackColor = vbRed
End If
End Sub

Suggestions: Avoid spaces in your object names,and always name a field something
meaningful. You appear to have used the same name (Start On Site) for both fields
involved, from separate tables, so interpreting your code is difficult.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Apples76]

Hi Al,

I have modifed my fieldnames in the underlying query so that they are all
unique and have tried using the follwing code but to no avail. do i actually
need to change the field names in the underlying table instead?

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


If IsNull(Actual_SOS) And Gen_SOS <= Now() Then

Actual_SOS.BackColor = vbRed
Else
If Actual_SOS > Gen_SOS Then
Actual_SOS.BackColor = vbRed

End If
End If
End Sub

 

[Al Campagna]

Apples,
Your syntax is incorrect. You have an Else, and then an IF on the next line. The
proper expression is ElseIf.
Your AND statement is also incorrect.

If IsNull(Actual_SOS) <= Now() And Gen_SOS <= Now() Then
Actual_SOS.BackColor = vbRed
ElseIf Actual_SOS > Gen_SOS Then
Actual_SOS.BackColor = vbRed
End If

As I wrote in the last post...Because you did not give sample values for these fields, and did not explain what each
field represented, and the logic of what your trying to accomplish... I can not be sure if
the IF logic is correct... I can only advise as to syntax.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."