Birthday query

[ruan567]

Hi everybody,

I posted this problem in another section as well, since it originally started out as something different. So I moved it here now.

Some pseudocode:

If (birthdate.month and birthdate.day) between (fromvalue.month and fromvalue.day) and (tovalue.month and tovalue.day)
Then display record.

where birthdate, fromvalue and tovalue are all defined as date?

Is this possible? Can one reference and compare the *combination* of day and month with another combination of day and month? Remember, the year must not be compared, which complicates things quite badly....

Any help with this would be greatly appreciated - this has been giving me problems for daays now

Regards
Ruan

 

[ruan567]

Solution!

Found this from access solutions and thought I should share it in case anybody has the same problem ever again:

Microsoft Access Database Solutions - Selecting birthdays within the next 30 days:

The following example shows how to create a query in SQL (Structured Query Language) that will select all birthdays that are due to fall in the next 30 days. This example assumes that you have a table named Employees and that this table includes a field named Birthdate.

As usual, there are many ways to perform the same action.
This select statement can be pasted straight into Query By Example sql view but don't forget to change table (Employees) and field name (Birthdate) ...

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

Run the query to validate your results

Regards
Ruan

 

[Brianwarnock]

Sorry to pour some cold water on this but
Int((Now()-[birthdate])/365.25)
is not a good way to calculate age, it fails around birthdays unless the required number of leap years have passed. Think about it

DOB= 01/01/2003
Date= 01/01/2004
Age1
days 365

Look up age calculation on the forum

Brian