Birth Day Range

[jasn_78]

I have a form where i am doing an option to show all members who have a birth date in a selected date range.
For some reason my attached query is still choosing all dates help please

SELECT ACMFTBL.ACMF_NUMBER, ACMFTBL.ACMF_NAME, ACMFTBL.ACMF_ADDRESS_1, 
ACMFTBL.ACMF_ADDRESS_2, ACMFTBL.ACMF_ADDRESS_3, ACMFTBL.ACMF_POST_CODE, 
ACMFTBL.ACMF_PHONE_NO, ACMFTBL.ACMF_FAX_NO, ACMFTBL.ACMF_FIRST_NAME, 
ACMFTBL.ACMF_SURNAME, ACMFTBL.ACMF_TITLE, ACMFTBL.ACMF_DATE_OF_BIRTH, 
FROM ACMFTBL
WHERE (((Format([ACMF_DATE_OF_BIRTH],"dd/mm")) 
	Between Format([Forms]![frmMEMBERSALE]![txtDATEFROM],"dd/mm") 
	And Format([Forms]![frmMEMBERSALE]![txtDATETO],"dd/mm")));
GROUP BY ACMFTBL.ACMF_NUMBER, ACMFTBL.ACMF_NAME, ACMFTBL.ACMF_ADDRESS_1, 
ACMFTBL.ACMF_ADDRESS_2, ACMFTBL.ACMF_ADDRESS_3, ACMFTBL.ACMF_POST_CODE, 
ACMFTBL.ACMF_PHONE_NO, ACMFTBL.ACMF_FAX_NO, ACMFTBL.ACMF_FIRST_NAME, 
ACMFTBL.ACMF_SURNAME, ACMFTBL.ACMF_TITLE, ACMFTBL.ACMF_DATE_OF_BIRTH

 

[jal]

I'm not good with dates - but wouldn't the problem be that you haven't specified the year?

You have:

Format([ACMF_DATE_OF_BIRTH],"dd/mm"))

In fact I don't understand why you need to format the date in a WHERE clause. Seems to me you should just compare the dates, not format them.

 

[jasn_78]

jai, sorry im not after birthdates in that year just the day/month range for printing birthday cards and emails etc.

so example find all members who have a birthday in the month of october no matter the year

 

[jal]

If there is no year, I should think that BETWEEN is confused. I think you need to go a different route (this is beginning to sound complicated) but I'm not sure what route - I'll give it a little thought.

 

[jal]

Why don't you just throw in the current year?

When no year is specified, CDate will presume the current year, I'm guessing. So wrap your format statements in CDate:

CDate(Format([ACMF_DATE_OF_BIRTH],"dd/mm")