Select birthdays within the next 30 days 1

[jmc014]

Hi there,

Ref: faq181-1374

Being interested in being able to select birthdays and or special dates within a date frame I came across the stated faq. I have created a table called Employees which also contains a field called Birthdate, here is the SQL View:


SELECT Employees.Birthdate,

Int((Now()-[birthdate])/365.25) AS Age,

[Birthdate]+((Int((Now()-[birthdate])/365.25)+1)*365.25) AS NextBirthday,

(Int((Now()-[birthdate])/365.25)+1) AS AgeNextBirthday, [Age]+1 AS Next

FROM Employees

WHERE ((([Birthdate]+((Int((Now()-[birthdate])/365.25)+1)*365.25)) Between Now() And Now()+30));


I have a birthdate which is 28/10/1970 and funny enough I get 40113,75 as [Nextbirthday] ????

I have been looking at all parameters and I am unable to figure out why
Can anyone throw some light on this subject for me.

Thanks,

 

[SkipVought]

Well that value, 40113, is 10/27/2009.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmc014]

Thanks Skip,

Sometimes the answer is wright in front of your eyes but cannot be seen... All sorted, thanks.

 

[SkipVought]

Just trying to be frank & wright as the architect of anwers.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmc014]

Sorry but I have to come back on this subject:

If the Birthdate is let's say 21/10/1970 the NextBirthday is showing 20/10/2009. This is ok if you wife has got bought you a Ferrari for your birthday and you want to drive it a day earlier?

 

[SkipVought]

You may simply need to add one day. Test to make sure this works in every case.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmc014]

Hi there,

Yep, tried that. If the birthdate is 21/10/2008 then the count would be +1 day?
I’m gonna play around with this for a while, let’s see what I can come up with.

Thanks,

 

[jmc014]

Hi there,

There is a thread that covers this subject: Upcoming Birthdays
Great stuff.

All solved.
Thanks for your help.