Link to home
Start Free TrialLog in
Avatar of kchoed
kchoed

asked on

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

"Select * from Staff Where BirthDay between Date - 7 And Date + 7" ?
or

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

ASKER

Hi,

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

Thanks
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())
SQL = "Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date) & " And Year(BirthDay) = Year(Date)" ?
Avatar of kchoed

ASKER

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
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 ..?
Avatar of kchoed

ASKER

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
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

ASKER CERTIFIED SOLUTION
Avatar of skhorshid
skhorshid

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kchoed

ASKER

hi skhorshid

Thanks a lot , it is very useful for me

kchoed