Get Birthday

Hi Expert,

I would like like write a function to get Staff Birthday whose birthday within -7<=NOW<=7

I face difficult when the birthday at the year end and how can i compare it correctly?

I use Access as my database to store the data,

Thanks
kchoed
Who is Participating?

Commented:
This will work if you have a table call staff and a dob date/time field,  I have tested this in access 2000

The date from parameter is mm

Basically it gets the day and month from the dob field adds it to the this year, the year before and the next year and calculates if the new date/s are within 7 days of mm,  the input date parateter

SELECT DISTINCT staff.*
FROM staff
or
or
0

Commented:
"Select * from Staff Where BirthDay between Date - 7 And Date + 7" ?
0

Commented:
or

"Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date)
0

Author Commented:
Hi,

I just need to compare the Day and Month, no need to compare YEAR, so how can i do it ?

Thanks
0

Commented:
Hi kchoed, this should work - here i am substituting the year of the DOB with the current year, so you will get the result you want. You can execute this query from vb and get your result.

SELECT Name, DOB  from Applications where
Month(DOB) & '/' & Day(DOB) & '/' & Year(Date()) >= DateAdd("d",-7,Date())
and Month(DOB) & '/' & Day(DOB) & '/' & Year(Date()) <= DateAdd("d",7,Date())
0

Commented:
SQL = "Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date) & " And Year(BirthDay) = Year(Date)" ?
0

Author Commented:
Hi ALL,

Thanks for your propsed answer first, but I have face the problem , when today is 01/01/2002, I can't compare one birthday with 26/12

Any suggestion?

Thanks
kchoed
0

Commented:
Sorry, try this:

SQL = "Select * from Staff Where BirthDay between #" Format\$(dateadd("d",-7,date),"mm/dd/yyyy") & "# And #" & Format\$(dateadd("d",7,date),"mm/dd/yyyy") & "#"

or what exact you want ..?
0

Author Commented:
Hi ryancys

I just want to compare the Month and Day with Today, but when today like "01/01", it can't compare the Staff Birthday with date like 26/12

Thanks for suggestion
kchoed
0

Commented:
If Current Year = 2002

Is '26/12' belong to 2001 ?

What is the Data Type of 'Birthday'? A Date?

Please give an example of 'Birthday' value in database.

regards

0

Author Commented:
hi skhorshid

Thanks a lot , it is very useful for me

kchoed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.