CRITERIA FOR DATES QUESTION

[steve goodrich]

A field in my staff database is "Date of Birth"

I would like to run three queries/reports

1 birthdays today

2 birthdays this month

3 who is 60 years old this month

Can I do this by entering criteria in a query, and if so how do I enter the
text. I assume it would be based on "date()" in the date of birth field
somehow

no code please, I just don,to understand it at all.

many thanks

Steve

 

[fredg]

A field in my staff database is "Date of Birth"

I would like to run three queries/reports

1 birthdays today

2 birthdays this month

3 who is 60 years old this month

Can I do this by entering criteria in a query, and if so how do I enter the
text. I assume it would be based on "date()" in the date of birth field
somehow

no code please, I just don,to understand it at all.

many thanks

Steve

You only need one query.
In a query, include the FirstName, LastName and Date of Birth [DOB]
fields.

Add a column:
BirthdaysToday:IIf(Format([DOB],"mm/dd") =
Format(Date(),"mm/dd"),"Today","")

Add another column:
ThisMonth:IIf(Format([DOB],"mm") = Format(Date(),"mm"),"This
Month","")

Add another column:

In a query:
Over60: IIf(DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB],
"mmdd") > Format(Date(), "mmdd"), 1, 0) >= 60 AND Format([DOB],"mm") =
Format(Date(),"mm"),"Over 60 this Month","")

 

[steve goodrich]

Thanks for the info Fred