Calculating Birthdays in Next 6 Weeks?

[Maillme]

Hi there,

Apologies for this relatively simple request - but i'm not quite sure how to do this.

I have a field in my record: dob

I'm looking to run a query that will tell me all the birthdays in the next 6 weeks from when i run the query - but i;ve no idea the syntax for this?

any help you can give would be greatly appreciated.

thanks.
Neil

 

[PHV]

I'd a look at the DateAdd function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Maillme]

Hi PHV,

thanks for you're reply. I see the function, but I am not quite sure how to build this into my query?

 

[Golom]

As long as your range is within the same year, this is fairly simple. Just a WHERE clause of the form

WHERE Format(DOB, "MMdd") >= Format(Date, "MMdd") 
  AND Format(DOB, "MMdd") <= Format(DateAdd("d", Days, Date), "MMdd")

where "Days" is the number of days in the future (6 weeks = 42 days)

If however, your number of days from today would force you into the next year then it gets more complicated and you need a User-defined function (UDF) something like this

Public Function BirthDayInRange(Dob As Date, Days As Integer) As Boolean
    Dim DaysToYearEnd               As Integer
    Dim DaysToEndDate               As Integer
    Dim DOBMonthDay                 As String
    Dim TodayMonthDay               As String

    DOBMonthDay = Format(Dob, "MMdd")
    TodayMonthDay = Format(Date, "MMdd")

    ' If the range would span the year end then split the test into two parts.
    ' - Current date to December 31 and compute the number of days used.
    ' - January 1 to the number of days necessary to make up the specified number.
    If Year(DateAdd("d", Days, Date)) > Year(Date) Then
        DaysToYearEnd = DateDiff("d", Date, DateSerial(Year(Date), 12, 31))
        DaysToEndDate = Days - DaysToYearEnd
        If (DOBMonthDay >= TodayMonthDay And DOBMonthDay <= Format(DateAdd("d", DaysToYearEnd, Date), "MMdd")) _
           Or (DOBMonthDay >= "0101" And DOBMonthDay <= Format(DateAdd("d", DaysToEndDate, dateserail(Year(Date) + 1, 1, 1)), "MMdd")) Then
            BirthDayInRange = True
        End If
    Else
        If DOBMonthDay >= TodayMonthDay And DOBMonthDay <= Format(DateAdd("d", Days, Date), "MMdd") Then
            BirthDayInRange = True
        End If
    End If
End Function

and your WHERE clause would be

WHERE BirthDayInRange([DOB], 42) = TRUE

I haven't tested that function and you will need to do that.

 

[PWise]

What I would do is create a calander table with all dates from
dayofyear
1/1/2000
1/2/2000
....
....
.....
12/30/2100
12/31/2100

and create this query

Select name ,birthday
from birthday table
inner join calander table
on month(birthday)=month(dayofyear)
and day(birthday)=day(dayofyear)
and dateadd("wk",6,dayofyear) between date() and dateadd("wk",6,date())

 

[PHV]

SELECT ...
FROM ...
WHERE DateSerial(Day([DOB field]),Month([DOB field],Year(Date())) Between Date() And DateAdd("ww",6,Date())

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886