Query on month in date

[Jim Shaw]

BlankI need a SELECT statement that will return individuals whos birthdate
falls in a specified month.
When I set up a query (to generate the SQL statements for me), the
month(birthdate) function does not seem to be acceptable in the "Criteria"
for the birthdate field.
I'm hoping I can use the generated SQL in my Dynamic query I'm coding in
VBA.
Perhaps I'm not coding it correctly?
Can anyone help?

Thanks
Jim

 

[PC Datasheet]

Assuming your birthdate field is DOB, put the following in a blank field in
your query:
BirthMonth:Month(DOB)
To get the records where birthday is in November, set the criteria to 11.

 

[Jim Shaw]

Thanks! That worked great. Now for the next level of complexity if you
will?

I now need to select individuals whose birthdate's anniversary falls between
two specified dates so I can generate a rolling period Birthday List report.
For example: I'm looking for individuals (born several years ago) who's
birthday anniversary falls between two run-time specified dates (such as:
9/23/2004 and 2/12/2005).

Your help is appreciated greatly
Jim

 

[Jim Shaw]

Disregard my question, I figured it out.
It can't be done inside of a reqular query so, using VBA, I travers the
record set in a do loop.

if month(rs.birthdate) < month(PeriodStartDate) then
Datetotest = month(rs.birthdate) & "/" & day(rs.birthdate) & "/" &
year(PeriodStartDate) + 1
else
Datetotest = month(rs.birthdate) & "/" & day(rs.birthdate) & "/" &
year(PeriodStartDate)
endif
if Datetotest >= PeriodStartDate AND Datetotest <= PeriodEndDate then
' you got a hit on a birthday which is within the period of interest
endif