I am using Access 2002. I have a simple database of tasks with due dates. I have a report which shows the tasks and are sorted by Due Date. I wanted to use Conditional Formatting to highlight red the tasks which have a due date past today and yellow the tasks due in the next two weeks. It appears the Conditional Formatting feature in Access only works with specific words or numbers and does not work with code like =TODAY(). The =TODAY() code works fine with conditional formatting Excel 2002, but in Access I get an error message saying "The expression you entered contains an invalid syntax." Can anyone help or offer any suggestions?

For today's date it would just be = Date

Sorry but that does not work either. I also tried =Day() and it does not work either. Does anyone have any other ideas how I might make this work? Surely I am not the only one with this problem.

Thanks,
Jim

Try > Now()

Nope. That does not work either. Anyone else have any suggestions. I am surprised this works so well in Excel but not in Access.

Thanks,
Jim

Is there any other way to accomplish this?

I don't have that "wonderful" creation but I'd guess the easy way is to create the numeric condition in the underlying query or in vba. Something like Iif([DateField]<Date(),1,2) add the field to the report and use that.
HTH

In the conditional formatting dialog
enter:

Date()

No equal sign and with brackets please.

THAT WORKED! Thanks for the help. I was about to think this would not work for some reason. I really appreciate the support here.

Jim

How could you highlight the entire record on the report though... all this does is highlights the date. Is there a way to link it up with other fields??

Yes I would like to know if there is a way to do this too. I have asked in the past and no one seemed to have an answer. However, its been a while. So if anyone knows how to make this highlight the whole row on conditional formatting reports, please let us know.

Thanks,
Jim

I was just working on a simular problem...I have A97 so I have no conditional format options that I know of. Here is what I did in VBA adjust it to your needs:
In the detail onformat in properties I put the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text23 <= 5 Then
Detail.BackColor = vbYellow
Else
Detail.BackColor = vbWhite
End If
End Sub

for me text23 was a row counter so I told it to highlight the detail row of the 1st 5 rows only. Text23 could be your criteria/date.