trying to retrieve a date between two dates (1 Viewer)

[DJBummy]

Thanks in advance

Searched birthday in threads and couldn't figure this out.

I have a select query and I am trying to retrieve a birthday that is between two dates.

This is how I set up my expressions:

chkBirthDay: Format([BirthDay],"m/d")

Criteria for chkBirthDay Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"

So if LaneDate = 9/7/2005 I should be able to retrieve all of the Birthdays that fall within 9/4 and 9/10. Correct?

I do retrieve records but they are dates such as 9/17/1921 and 9/18/1948 but of course they do not satisfy the criteria. There is a date in the table that does, 9/7/1981, but that record does not show up?

Any suggestions?

 

[FoFa]

Instead of Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"
try Between DateAdd("d",-3,([LaneDate]) And DateAdd("d",3,[LaneDate])

 

[EMP]

For Between ... And ... to work properly,
you have to format "mm/dd"

^

 

[DJBummy]

Thanks so much for the help

I tried both suggestions. When running the query I kept getting the expression was too complex error. Not sure why? So I changed back to my original expression in the criteria except I left the format at "mm/dd" as EMP suggested.

Between Format([LaneDate]-3,"mm/dd") And Format([LaneDate]+3,"mm/dd").
This worked perfectly.

In case anyone is wondering. This allows me to extract a bowlers birthday that falls between that weeks beginning and ending dates (Sunday 1st day and Saturday 7th day). We bowl Wednesdays.

Thanks again. I can now get back to my normal life of drinking beer and golfing. Not always in that order.

D.J.