check if date is on birthday week

[bambo]

Hi,

MS Access 2003: I'd like to make a query / VBA code that checks if a given date falls in the same calendar week as the person birthday (consider a week starts on Sunday and ends on Saturday).

for example: lets say my birthday is 18/10/2008 (so its coming this friday), and i want to check if the date 13/10/2008 falls on my birthday week then its true...

Thanks!

[anhn]

The simplest way is using DateDiff() with interval "ww"

w = DateDiff("ww", BirthDay, aDate, vbSunday)

If w = 0 then aDate and BirthDay are in the same week that starts on Sunday.

[bambo]

I'm not checking that the certain date actually happens on the week of my birth, but rather on the week of my birthday (so every year is ok, not just the my birth year)....

I guess what could work is to find the dates of Sunday, and of Saturday of the aDate, and then to compare the day and month of the birth date to this Sunday- Saturday range.

Question is how to check that the birthday (day and month) are inside this range of week start to week end (considering cases like end of the year)

Any ideas?

[Siddharth Rout]

Is this what you want?

Sub FindWeekNo()
'Change this date to check the week no
'Insert the date as # 13 / 10 /2008#
'Depending upon the system date and time it
'will automatically readjust as shown below
strdate = #10/13/2008#

week_no = "Week " & Int((13 + Day(strdate) - Weekday(strdate)) / 7)

MsgBox week_no

End Sub

[anhn]

Test this:

Function WOB(aDate As Date, DOB As Date, ThisBD As Date) As Integer
    ThisBD = DateSerial(Year(aDate), Month(DOB), Day(DOB))
    WOB = DateDiff("ww", ThisBD, aDate, vbSunday)
    If Abs(WOB) >= 51 Then
        ThisBD = DateAdd("yyyy", Sgn(WOB), ThisBD)
        WOB = DateDiff("ww", ThisBD, aDate, vbSunday)
    End If
End Function

Returns 0 if aDate and ThisBD are in the same week that starts on Sunday.