Quering a date mm/dd/any year to mm/dd/any year

[ImAimo]

Hi!

I have researched your previous posts. I have found one thread that is relevant to my problem. I tried what I thought Pat said to do. http://www.access-programmers.co.uk/ubb/Forum3/HTML/000353.html

He said - Use the format command to extract just the month and day portion of the date field - Where Format(YourDate,"mmdd") between "0416" and "0423".

Since I want the operator to be able to determine what dates they want report, here is what I typed in the criteria section of the design query:

Format("Bday","mmdd") Between [Enter Start Month & Day 00/00] And [Enter Ending Month & Day 00/00]

I just typed in Bday without the quotes, but access put them there when I left the criteria cell?

Am I suppose to put this in the criteria cell of the query or does it belong somewhere else?

When I run the query I get NO RESULTS.

I am a new access user and find your site VERY helpful, especially reviewing the older posts for help. Your site search is awesome!

Thanks for your help.
Amy

 

[Rich]

Why can't you just use dates?

 

[ImAimo]

Hi Rich,

Because if I enter 01/01/00 to 01/31/99 I get everybody in the database. All I want is those people that have birthdays in the month of January regardless of what year they were born. We send a birthday post card to everyone in our database. We do this on a monthly basis. We send all the post cards for one month at the same time.

I hope this helps.
Amy

 

[Rich]

Would you rather have a combo box with all the months listed and then display relevant records based on month selected?

 

[cfmiles]

The Format(Bday,"mmdd") should be in the field line and the Between....AND.... in the criteria.

It appears that you have put the whole thing in the criteria line. If I misunderstood, sorry!

hth
CM

 

[ImAimo]

CM - Thanks

Rich - Sure a combo list box would be great, but I don't know how to do that. I am still learning. Trying to research it now. Thanks for the idea!