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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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.
 
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)


Brendan Reynolds said:
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

Purny said:
Using Access 2003
Click to expand...
Click to expand...
 
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()
 
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

Douglas J. Steele said:
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)


Brendan Reynolds said:
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

Purny said:
Using Access 2003
Click to expand...
Click to expand...
Click to expand...
 
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)


Brendan Reynolds said:
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

Douglas J. Steele said:
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)


Brendan Reynolds said:
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
Click to expand...
Click to expand...
Click to expand...
 
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())
Click to expand...

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
 
Back
Top