|
|
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!
|
|
Try something like this in conditional formatting
Expression is DateDiff("y",[MyDate],Date())<26
"messingerjc" <messingerjc[ at ]discussions.microsoft.com> wrote in message
news:40AEDA58-24C0-441A-B957-1C8D3750D22A[ at ]microsoft.com...
[Quoted Text] >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 wrote:
[Quoted Text] > 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.
|
|
On Thu, 13 Nov 2008 21:50:45 -0800, messingerjc wrote:
[Quoted Text] > 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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
|
|
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!
"fredg" wrote:
[Quoted Text] > On Thu, 13 Nov 2008 21:50:45 -0800, messingerjc wrote:
>
> > 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.
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
|
|
|