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,

please help

Thanks
kchoed
kchoedAsked:
Who is Participating?
 
skhorshidConnect With a Mentor 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
WHERE (((Abs(DateDiff("d",DateAdd("d",Day([staff].[dob]),DateAdd("m",Month([staff].[dob])-1,DateAdd("yyyy",Year(Now())-1900,"31/12/1899"))),[mm])))<=7))
or
 (((Abs(DateDiff("d",DateAdd("d",Day([staff].[dob]),DateAdd("m",Month([staff].[dob])-1,DateAdd("yyyy",Year(Now())-1901,"31/12/1899"))),[mm])))<=7))
or
(((Abs(DateDiff("d",DateAdd("d",Day([staff].[dob]),DateAdd("m",Month([staff].[dob])-1,DateAdd("yyyy",Year(Now())-1899,"31/12/1899"))),[mm])))<=7));
0
 
Ryan ChongCommented:
"Select * from Staff Where BirthDay between Date - 7 And Date + 7" ?
0
 
Ryan ChongCommented:
or

"Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kchoedAuthor Commented:
Hi,

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

Thanks
0
 
riaz9Commented:
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
 
Ryan ChongCommented:
SQL = "Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date) & " And Year(BirthDay) = Year(Date)" ?
0
 
kchoedAuthor 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
 
Ryan ChongCommented:
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
 
kchoedAuthor 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
 
Ryan ChongCommented:
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
 
kchoedAuthor 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.

All Courses

From novice to tech pro — start learning today.