Extract day and month only from the date field

[philpbm]

I have a database with a table (memberdetails). I would like a user to run a monthly report to download all member records whose birthdays fall between the first and last day of the following month. The user will input a StartDate, e.g. 01/09/2011 and EndDate e.g. 30/09/2011. Both dates are input using dtpicker. I would like a query to extract all member details whose birthdays fall between 01/09 and 30/09 and display the birthdays only as day and month.

I am ok with the SELECT part of the query but the WHERE part is giving me problems. How can I write the WHERE part so that I use the day and month parts only of the short date to extract the data. Please see what I have at the moment:
WHERE ((tblMemberDetails.([DateofBirth],"dd mm",0,0)) Between [forms]![frmStatusReports]![frmDateSelection]!([BeginningDate],"dd mm",0,0) And [forms]![frmStatusReports]![frmDateSelection]!([EndingDate],"dd mm",0,0));

[geekgirlau]

WHERE DateSerial(Year(Date),Month([DateofBirth]),Day([DateofBirth]))
between DateSerial(Year(Date),Month([FormDateField]),Day([FormDateField]))
AND DateSerial(Year(Date),Month([FormDateField]),Day([FormDateField]))

With DateSerial, you take the month and day from your field, and just use the current year. In the interests of clarity I haven't used the correct name for the field on your form.

You may also need to work on a bit more logic to deal with a date range that spans more than one year. In fact this is a good situation in which to create a user defined function instead of building it in your native query.