Conditional Formatting Using Date To Verify Age

[messingerjc]

I have a table (Alpha Roster) that has misc. info about personnel. One of
the fields is Date Of Birth (DOB). I would like to use conditional
formatting on the dates to where the text color would change if they were
younger than 26. I have the DOB Field setup as a Medium Date.

When I run the report, I would like all the personnel to be listed,
regardless of age, in ABC order, but just have the ones that are under 26 to
have their DOBs listed in another color.

Any help on this would be greatly appreciated!

 

[Scott Lichtenberg]

Try something like this in conditional formatting

Expression is DateDiff("y",[MyDate],Date())<26

 

[Mike Painter]

Try something like this in conditional formatting

Expression is DateDiff("y",[MyDate],Date())<26

That will be wrong half the time on average. It will only be right if the
birthday has passed.
You need to calculate the age first.

Posted by Ken Getz: (sometime prior to 2001)

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken



I'd add the age in a query and add it to the form, then you can use Age<26
as needed.

 

[fredg]

I have a table (Alpha Roster) that has misc. info about personnel. One of
the fields is Date Of Birth (DOB). I would like to use conditional
formatting on the dates to where the text color would change if they were
younger than 26. I have the DOB Field setup as a Medium Date.

When I run the report, I would like all the personnel to be listed,
regardless of age, in ABC order, but just have the ones that are under 26 to
have their DOBs listed in another color.

Any help on this would be greatly appreciated!

Showing the Conditional Formatting dialog for the [DOB] control....
Set Condition1 to
Expression Is
write, in the next box (all on one line):
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)<26

Select the color.
Click OK.

 

[messingerjc]

Thank you very much for everyone's help. I was able to look at this from a
couple of different angles. The easier solution for me at my level was from
fredg. Don't know a lot about functions yet, but still learning. Thank you
again, it worked!

I have a table (Alpha Roster) that has misc. info about personnel. One of
the fields is Date Of Birth (DOB). I would like to use conditional
formatting on the dates to where the text color would change if they were
younger than 26. I have the DOB Field setup as a Medium Date.

When I run the report, I would like all the personnel to be listed,
regardless of age, in ABC order, but just have the ones that are under 26 to
have their DOBs listed in another color.

Any help on this would be greatly appreciated!

Showing the Conditional Formatting dialog for the [DOB] control....
Set Condition1 to
Expression Is
write, in the next box (all on one line):
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)<26

Select the color.
Click OK.