Query on month in date

  • Thread starter Thread starter Jim Shaw
  • Start date Start date
J

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
 
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.
 
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
 
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
 
Back
Top