Microsoft Access - How to use conditional formatting to highlight the birthdate of employees under 21 in a report

Question

Hi,

I have a database of my company's employees. I would like to use conditional formatting on a report to highlight the birthdates of all the employees that are under 21. Specifically, I want to highlight all the birthdates for employees who haven't turned 21 by July 15th on any given year.

I have selected the birthdate field on my report and, under conditional formatting, entered the following rule:

Nothing happens. Not a single employee is highlighted on my report, though I know a few are under 21. Any ideas where I went wrong and what I should do instead?

Thanks!

Answer 1

You might like to take a look at Age.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates the use of a number of age related functions, including a GetAge function which you can paste into a standard module in your database.  You can then easily create an expression for conditional formatting.  You can see how it would work by entering some literal values into the immediate window,  Firstly for a date of birth for someone over 21 on 15 July in the current year:

Birthday = #2000-07-04#

? GetAge(Birthday,True,DateSerial(Year(Date()),7,15))<=21

False

If we then change the value of Birthday so that the age will be under 21:

Birthday = #2002-07-04#

? GetAge(Birthday,True,DateSerial(Year(Date()),7,15))<=21

True

Answer 2

You have a table called "Hired"? And one of the fields in that table is "Birthday"? And that field is one of the records in the report's recordsource?

Answer 3

You have a table called "Hired"? And one of the fields in that table is "Birthday"? And that field is one of the records in the report's recordsource?

Yes, that is all correct.

Answer 4

TODAY() is not an Access VBA function. Date() is.

Answer 5

Try [Hired].[Birthday] <= DateAdd("yyyy", -21, DateSerial(Year(Date()), 7, 15)