how do I create a daily birthday list from a DOB field?

[Purny]

Using Access 2003

 

[RipperT]

SELECT tblBirthdays.*
WHERE tblBirthday.DOB = Date()

This will return a list of today's birthdays. If you want tomorrows or
another day's birthdays, substitute the Date() with a date like #4/22/2001#.
Be sure to enclose it with the # sign. I'm assuming your table is named
tblBirthday. If not, substitute your table name into the query.

HTH,

Rip

 

[Brendan Reynolds]

Here's an example from one of my own apps ...

SELECT tblStudents.LastName, tblStudents.FirstName, tblStudents.MiddleName,
tblStudents.DOB, tblStudents.Room, tblYear.YrName, tblTeachers.TeacherName
FROM tblTeachers RIGHT JOIN (tblStudents LEFT JOIN tblYear ON
tblStudents.YearNum = tblYear.YrNo) ON tblTeachers.TeacherID =
tblStudents.TeacherID
WHERE (((tblStudents.Dleft) Is Null) AND ((Month([DOB]))=Month(Date())) AND
((Day([DOB]))=Day(Date()))) OR (((tblStudents.Dleft)>Date()) AND
((Month([DOB]))=Month(Date())) AND ((Day([DOB]))=Day(Date())));

'DLeft' is the date the student left the school, so in addition to the DOB
field, the above query also checks that the DLeft field is either Null or
contains a date that is in the future.

 

[Douglas J. Steele]

Just curious, Brendan. Why not

WHERE (tblStudents.Dleft Is Null OR tblStudents.Dleft > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

or even

WHERE Nz(tblStudents.Dleft, Date() + 1) > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here's an example from one of my own apps ...

SELECT tblStudents.LastName, tblStudents.FirstName,
tblStudents.MiddleName, tblStudents.DOB, tblStudents.Room, tblYear.YrName,
tblTeachers.TeacherName
FROM tblTeachers RIGHT JOIN (tblStudents LEFT JOIN tblYear ON
tblStudents.YearNum = tblYear.YrNo) ON tblTeachers.TeacherID =
tblStudents.TeacherID
WHERE (((tblStudents.Dleft) Is Null) AND ((Month([DOB]))=Month(Date()))
AND ((Day([DOB]))=Day(Date()))) OR (((tblStudents.Dleft)>Date()) AND
((Month([DOB]))=Month(Date())) AND ((Day([DOB]))=Day(Date())));

'DLeft' is the date the student left the school, so in addition to the DOB
field, the above query also checks that the DLeft field is either Null or
contains a date that is in the future.

--
Brendan Reynolds
Access MVP

Using Access 2003

 

[Douglas J. Steele]

Of course, that's only going to give you people born today, not people whose
birthday it is.

To find out whose birthday is today, you'd want

SELECT tblBirthdays.*
WHERE Format(tblBirthday.DOB, "mmdd") = Format(Date(), "mmdd")

or

SELECT tblBirthdays.*
WHERE DateSerial(Year(Date), Month(tblBirthday.DOB), Day(tblBirthday.DOB)) =
Date()

 

[Brendan Reynolds]

It's years since I wrote that, Doug, so I really couldn't say whether there
was any specific reason why I chose that approach rather than one of the
alternatives. And I'm not entirely sure that I ever did write it exactly as
it now appears - I think Access may have rearranged it somewhat. Do you
think it makes a significant difference?

--
Brendan Reynolds
Access MVP

Just curious, Brendan. Why not

WHERE (tblStudents.Dleft Is Null OR tblStudents.Dleft > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

or even

WHERE Nz(tblStudents.Dleft, Date() + 1) > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here's an example from one of my own apps ...

SELECT tblStudents.LastName, tblStudents.FirstName,
tblStudents.MiddleName, tblStudents.DOB, tblStudents.Room,
tblYear.YrName, tblTeachers.TeacherName
FROM tblTeachers RIGHT JOIN (tblStudents LEFT JOIN tblYear ON
tblStudents.YearNum = tblYear.YrNo) ON tblTeachers.TeacherID =
tblStudents.TeacherID
WHERE (((tblStudents.Dleft) Is Null) AND ((Month([DOB]))=Month(Date()))
AND ((Day([DOB]))=Day(Date()))) OR (((tblStudents.Dleft)>Date()) AND
((Month([DOB]))=Month(Date())) AND ((Day([DOB]))=Day(Date())));

'DLeft' is the date the student left the school, so in addition to the
DOB field, the above query also checks that the DLeft field is either
Null or contains a date that is in the future.

--
Brendan Reynolds
Access MVP

Using Access 2003

 

[Douglas J. Steele]

To be honest, I don't know. It strikes me that

(Format([DOB], "mmdd") = Format(Date(), "mmdd"))

is going to be more efficient than

(Month([DOB]))=Month(Date()) AND (Day([DOB])=Day(Date())

since there are fewer function calls (especially since you've got that
twice), but as to whether it'll make a huge difference, who can say?

(Don't know what happened to the equal signs in what I originally posted!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It's years since I wrote that, Doug, so I really couldn't say whether
there was any specific reason why I chose that approach rather than one of
the alternatives. And I'm not entirely sure that I ever did write it
exactly as it now appears - I think Access may have rearranged it
somewhat. Do you think it makes a significant difference?

--
Brendan Reynolds
Access MVP

Just curious, Brendan. Why not

WHERE (tblStudents.Dleft Is Null OR tblStudents.Dleft > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

or even

WHERE Nz(tblStudents.Dleft, Date() + 1) > Date())
AND (Format([DOB], "mmdd") Format(Date(), "mmdd")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here's an example from one of my own apps ...

SELECT tblStudents.LastName, tblStudents.FirstName,
tblStudents.MiddleName, tblStudents.DOB, tblStudents.Room,
tblYear.YrName, tblTeachers.TeacherName
FROM tblTeachers RIGHT JOIN (tblStudents LEFT JOIN tblYear ON
tblStudents.YearNum = tblYear.YrNo) ON tblTeachers.TeacherID =
tblStudents.TeacherID
WHERE (((tblStudents.Dleft) Is Null) AND ((Month([DOB]))=Month(Date()))
AND ((Day([DOB]))=Day(Date()))) OR (((tblStudents.Dleft)>Date()) AND
((Month([DOB]))=Month(Date())) AND ((Day([DOB]))=Day(Date())));

'DLeft' is the date the student left the school, so in addition to the
DOB field, the above query also checks that the DLeft field is either
Null or contains a date that is in the future.

--
Brendan Reynolds
Access MVP

Using Access 2003

 

[Tim Ferguson]

To be honest, I don't know. It strikes me that

(Format([DOB], "mmdd") = Format(Date(), "mmdd"))

is going to be more efficient than

(Month([DOB]))=Month(Date()) AND (Day([DOB])=Day(Date())

I wasn't so sure: a string comparison sounds much slower than an integer
one. I tried the code below, and the integer comparison takes 70-100% as
long as the string version. YMMV of course.

Testing code follows:

All the best


Tim F

--

Public Function CompareByString(Date1 As Date, Date2 As Date)
CompareByString = (Format(Date1, "mmdd") = Format(Date2, "mmdd"))
End Function

Public Function CompareByInteger(Date1 As Date, Date2 As Date)
CompareByInteger = (Month(Date1) = Month(Date2) And Day(Date1) = Day
(Date2))
End Function

Public Function TimedComparisons()

Const iters As Long = 1000000
Const startDate As Date = "1900-01-02"

Dim i As Long
Dim d1 As Date, d2 As Date
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date

d2 = Date
d1 = startDate
t1 = Now
For i = 1 To iters
Call CompareByString(d1, d2)
d1 = d1 + 1
Next i
t2 = Now

d1 = startDate
t3 = Now
For i = 1 To iters
Call CompareByInteger(d1, d2)
d1 = d1 + 1
Next i
t4 = Now


Debug.Print "By string: "; (t2 - t1) * 24 * 60 * 60
Debug.Print "By integer: "; (t4 - t2) * 24 * 60 * 60

End Function