Birthday Report (3 Viewers)

[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.

 

[Eezeepc]

Moke123 - thank you so much. I will try and emulate your solution.

 

[Mark_]

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

He's trying to figure out what their birth day is this year based on each date of birth. Basically sawing off the "Year" and plugging in the current one.

 

[Gasman]

He's trying to figure out what their birth day is this year based on each date of birth. Basically sawing off the "Year" and plugging in the current one.

So you can only look in the current year with that logic?
Anyone next month, he will have to wait until New Year, and run it then.

OR, he could just compare month and day and hope noone was born on the 29th February.
My birthday this year was 19/07/2025, it will be the same day and month next year, just the year will change.

ChatGPT offers this

Function BirthdayInRange(BirthDate As Date, StartDate As Date, EndDate As Date) As Boolean
    Dim bdayThisYear As Date
    Dim bdayNextYear As Date
    
    bdayThisYear = DateSerial(Year(StartDate), Month(BirthDate), Day(BirthDate))
    bdayNextYear = DateSerial(Year(StartDate) + 1, Month(BirthDate), Day(BirthDate))
    
    BirthdayInRange = _
        (bdayThisYear >= StartDate And bdayThisYear <= EndDate) Or _
        (bdayNextYear >= StartDate And bdayNextYear <= EndDate)
End Function

 

[Gasman]

Using the above, this seems to work?

SELECT SalesRecord.[Item Type], Format([Order Date],"ddd dd/mm/") & IIF(Month([Order Date]) < Month(Date()),2026,2025) AS Expr2
FROM SalesRecord
WHERE (((BirthdayInRange([Order Date],#7/19/2025#,#1/4/2026#))=True));

I had to use what I had, so Orderdate is the birthdate, and I just hardcoded the date range

 

[Ken Sheridan]

OR, he could just compare month and day and hope noone was born on the 29th February.

There's no need to worry about birthdays on 29 February. The DateSerial function handles that, returning a date on 1 March in non-leap years, e.g.

? DateSerial(2025,2,29)
01/03/2025

There is no international consensus as to what is the anniversary of 29 February in a non-leap year. In some countries it's 28 February, in others it's
1 March, but most use the latter. In the following function I allow the user to choose by passing a Boolean value into the function as an argument:

Public Function GetAge(varDoB As Variant, blnFebLeapAnniversary As Boolean, Optional varAgeAt As Variant) As Variant

    ' Returns:  Age in years as an integer.
    ' Accepts:  varDoB; date/time value
    '           blnFebLeapAnniversary; Boolean constant, TRUE if anniversary
    '           of 29 February birthdays is 28 February, FALSE if is 1 March.
    '           varAgeAt; date/time value, optional; date at which age to be computed,
    '           defaults to current date.
   
    If Not IsNull(varDoB) Then
        If IsMissing(varAgeAt) Then varAgeAt = VBA.Date
       
        GetAge = DateDiff("yyyy", varDoB, varAgeAt) - _
            IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0)
   
        ' adjust for leap year if necessary
        If blnFebLeapAnniversary Then
            If IsLeapDate(varDoB) And Month(varAgeAt) = 2 And Day(varAgeAt) = 28 And Not IsLeapDate(varAgeAt + 1) Then
                GetAge = GetAge + 1
            End If
        End If
    End If
   
End Function

The function calls the following function to determine if a date of birth is on 29 February in a leap year:

Public Function IsLeapDate(ByVal dtmDate As Date) As Boolean

    If Month(dtmDate) = 2 And Day(dtmDate) = 29 Then
        IsLeapDate = True
    End If
   
End Function