Conditional Formatting 2

[EllieFant]

Hi,

I have put an expression in a query (NACDue: [HireDate] + 60) so that I know the due date for an employee to turn in their NAC (National Agency Check). This works great.

I have created a report based off the query. I want to know when someone is over due in getting their NAC submitted to our department. I have put the following code in the on format event of the details section of the report.

If CDate(NACDue) < Date() Then
NACDue.ForeColor = vbRed
Else
NACDue.ForeColor = vbBlack
End If

I get an Compile Error: Method or data member not found. The .ForeColor is selected when I click on ok. I have asked Paul Bricker about this (and he had given me the above code) but since I am sitting at my laptop and don't have an email address programed on it yet, I thought I would post my question here.

Thanks in advance for your help.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[Norris68]

Try using custom formatting. From Access 2000 help:

Custom number formats can have one to four sections with semicolons ( as the list separator. Each section contains the format specification for a different type of number.

Section Description
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null values.

For example, you could use the following custom Currency format:
$#,##0.00[Green];($#,##0.00)[Red];&quot;Zero&quot;;&quot;Null&quot;
This number format contains four sections separated by semicolons and uses a different format for each section.

 

[PaulBricker]

L, check your references also. Open the module with your code and go to Tools...References and see what you have selected for libraries. The ones with the checkmark are the ones you have available. Let us know what they are.

Paul

 

[EllieFantHome]

Norris - Thanks for the advice. Although I am at home working on 2000 I need for this to work on 97 so was trying to make it work without using the new conditional formatting of 2000.

Paul - Thanks for the tip. I will try that when I get home from work. I will let you know if it doesn't work at work also.

Have a great day!

 

[EllieFantHome]

Paul,

Under references, the first 6 are marked. There appears to be LOTS and LOTS that are not.

 

[Norris68]

The custom number formats worked back in Access V2.0! It definately works in 97.

 

[CosmoKramer]

Ellie,

What is the name of the text box that holds NACDue?? That's the name of your expression field, but is it also the name of the control??

 

[EllieFant]

OK, now I feel DUMB!!

I used a report that my boss had created before I got into this job. He used spaces in his field names and I didn't change the field name, just the control source. He used spaces in his field name...took the spaces out and it worked great!

Thanks for the help.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net