Birthday alerts 4

[captsamm]

I need a query that will produce a list of people from my table that have a birthday within a two week window that starts one week from the day I run The query. ie; if I run the query today, Oct 9th, the query should list everyone with a birthday beetween the 16th and 30th of October.
Can anyone help please...

Thanks

Samm

 

[SgtJarrow]

Create the Query as you want it...in the criteria field for the birthdate, put:

Between Date()+7 And Date()+21

This will take todays, date, add seven, make it the lower limit, the take todays date and add 21 to make it the higher limit Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[captsamm]

Thanks, but...Well, if all the people in the table were going to be born in the time period that is specified that would work, but all of them were born in the 40s, 50s, 60s, 70s and 80s. The year is the stumbling block here. It is also a problem on the year end/beginning time period also.

Thanks, Samm

 

[nat1967]

Hi,

You need to check out MVP Arvin Meyer's website. He has the exact thing you are looking for. You can download his database example and it works great for me.

http://www.datastrat.com/Download2.html

There are several example databases but look for the one marked Birthday.

HTH

Nathan

 

[SgtJarrow]

I think you can do this pretty easily also, just depends on your VBA coding level....

What you are going to need to do is find lowest year(for example 1953) and the highest year (1985) The use those years as part of your criteria.....

Give me a few minutes and I'll put together a sample database....send me an e-mail to the address in my sig block and I will forward it to you when done.... Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[SgtJarrow]

Actually, this was much simplier than I originally anticipated....

All you want to display is anyone with a birthday during the month in question and between today+7 and today+21...There is no need to even worry about the year of birth......

So assuming a table called Table1 with two fields, Name and BithDate, the following SQL will extract anyone with a birthday between the above dates....

SELECT Table1.Name, Table1.BirthDate
FROM Table1
WHERE (((Month([Table1]![BirthDate])) Between Month(Date()+7) And Month(Date()+21)) AND ((Day([Table1]![BirthDate])) Between Day(Date()+7) And Day(Date()+21))); Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[r937]

robert, your last solution might not work if today is december 23 and the person's birthday is january 3

Month([Table1]![BirthDate]))
Between Month(Date()+7) And Month(Date()+21)

will resolve to 01 between 12 and 01

Day([Table1]![BirthDate]))
Between Day(Date()+7) And Day(Date()+21)

will resolve to 03 between 30 and 13


i wrote up a solution which uses a quirk of mysql, this would probably require an IIF term in access --

Birthdays in the next two weeks

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid458485_tax285649,00.html

rudy

http://rudy.ca/

 

[JaneInMA]

The above solution gives a month time period.
What you need is to format the birthdate by month and day in the field part of the query builder ie Expr1: Format([birthdate],"mm-dd&quot then in the criteria apply similar formatting Between Format((Date()+7),"mm-dd&quot And Format((Date()+21),"mm-dd&quot. The statement above give the birthdays 1-3 weeks away.
Hope this helps

 

[captsamm]

Jane in MA,
Thanks for the simple and elegant solution. I have put a star on your post because it was such a simple answer to a question many others had foundered upon. Yours is the only star I issued.


Thanks again for all the help everyone....


Samm