Birthday Report (1 Viewer)

[Eezeepc]

I have a Report which uses a start date and end date enterered via a Form and this creates a list of birthdays that fall within the date range specified.
Here is the Query:

The problem is that when the end date is in the following year, the Query fails because it is locked to the current year by the DateSerial function.
Is there a better way to extract birthdates that occur after a year end? I am not a programmer as you can tell.
Thanks for any help.

 

[moke123]

For what reason are you using dateserial? And using date() in the year criteria rather than BDate?

 

[Gasman]

Just use dates?
Surely their birthday is on the same day and month (unless Feb 29 or dead?)

 

[moke123]

Something like this?

 

[Ken Sheridan]

You could just loop through all possible birthdays for a given date, and see if one falls within the date range:

Public Function GetBirthDay(dtmDoB As Date, dtmFrom As Date, dtmTo As Date) As Boolean

    Dim n As Integer
    Dim dtmBirthday As Date
   
    For n = 0 To 120
        dtmBirthday = DateAdd("yyyy", n, dtmDoB)
        If dtmBirthday >= dtmFrom And dtmBirthday < dtmTo+1 Then
            GetBirthDay = True
            Exit For
        End If
    Next n

End Function

In a query you'd call it like this:

PARAMETERS [Start of date range:] DATETIME,
[End of date range:] DATETIME;
SELECT *
FROM Contacts
WHERE  GetBirthDay (DoB, [Start of date range:], [End of date range:]);

In reality the parameters would usually be references to controls in a form of course.