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
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
"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)
"Select * from Staff Where BirthDay between " dateadd("d",-7,date) & " And " & dateadd("d",7,date)
ASKER
Hi,
I just need to compare the Day and Month, no need to compare YEAR, so how can i do it ?
Thanks
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())
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)" ?
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
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,dat e),"mm/dd/ yyyy") & "# And #" & Format$(dateadd("d",7,date ),"mm/dd/y yyy") & "#"
or what exact you want ..?
SQL = "Select * from Staff Where BirthDay between #" Format$(dateadd("d",-7,dat
or what exact you want ..?
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi skhorshid
Thanks a lot , it is very useful for me
kchoed
Thanks a lot , it is very useful for me
kchoed