query to retrieve members with a birthday in the next n weeks

[DaJoin]

All,

I have a table with member data.
I wanna retrieve all member that have their birthday in the next n weeks.

I have one problem i cannot get solved :

Today + 1 week shows me the correct data.
Today + 2 weeks also works well
(combining datepart and dateadd functions to compare the dates)

When I try today + 52 weeks, in stead of showing me all members, I only get the members having their birthday between now and the end of the year. I understand why this is so, but I do no know how to solve the problem ...
Any help ?

 

[MichaelRed]

52 (or even 520) weeks should be o.k. I would use dateserial instead of the datepart, the where part should look something like:

Where DOB Between DateSerial(Year(StartDt), Month(DOB), Day(DOB ) and Dateadd("ww", NWks, StartDt) MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[danvlas]

Try the following Where statement:

WHERE (((DateValue(Format([TableName]![DateFieldName],&quot;mmm&quot & &quot; &quot; & Day([TableName]![DateFieldName])))>=Date() And (DateValue(Format([TableName]![DateFieldName],&quot;mmm&quot & &quot; &quot; & Day([TableName]![DateFieldName])))<=DateAdd(&quot;ww&quot;,[how many weeks],Date()))) OR (((DateValue(Format([TableName]![DateFieldName],&quot;mmm&quot & &quot; &quot; & Day([TableName]![DateFieldName]) & &quot;, &quot; & Year(DateAdd(&quot;ww&quot;,[how many weeks],Date()))))>=Date() And (DateValue(Format([TableName]![DateFieldName],&quot;mmm&quot & &quot; &quot; & Day([TableName]![DateFieldName]) & &quot;, &quot; & Year(DateAdd(&quot;ww&quot;,[how many weeks],Date()))))<=DateAdd(&quot;ww&quot;,[how many weeks],Date())));

You need to calculate the birthday falling on current year and the birthday falling on the year of the ending period. Then you have to make sure that those dates are >= current date and <= ending date.

Make sure the parameter [how many weeks] is entered THE SAME in all four places, otherwise you'll be prompted more than necessary.

Hope didn't miss something...

Dan

 

[DaJoin]

Thanks guys !