Upcoming Birthdays

[skoutr1]

Hi all-

I have an MS Access database with a table that contains employee information, including a field for date of birth (dteDOB).

I want to run a query from this table and only display employees that have a birthday occuring within the next 60 days.

This does not seem to be working for me...

Query Field 'NextBDay'

NextBDay: IIf(Month(Date())>Month([dteDOB]) Or (Month(Date())=Month([dteDOB]) And Day(Date())>=Day([dteDOB])),"#"&Month([dteDOB])&"/"&Day([dteDOB])&"/"&Year(Date())+1&"#", "#"&Month([dteDOB])&"/"&Day([dteDOB])&"/"&Year(Date())&"#")

Criteria: >Date() AND <=Date()+60

Any insight is appreciated.
-Rod

 

[PHV]

NextBDay: DateSerial(Year(Date()+IIf(Format([dteDOB],'mmdd')>=Format((Date(),'mmdd'),0,1)), Month([dteDOB]), Day([dteDOB]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[skoutr1]

PH-

After tweaking the code you supplied and searching through help files this is what finally worked:

==========

NextBDay: DateSerial(Year(Date())+IIf(Format([dteDOB],'mmdd')>=Format(Date(),'mmdd'),0,1),Month([dteDOB]),Day([dteDOB]))

Criteria: Between Date() And DateAdd("d",60,Date())

==========

Thanks for the help.
-Rod

 

[PHV]

Sorry for the missing )

 

[skoutr1]

I think it was an extra ) that was in there. No big deal. Access picked that up right away.

I kept trying to tweak the NextBDay and Criteria statements because I kept getting a data mismatch error. In the end, your code was fine, my criteria statement was the problem.

Thanks again.
-Rod