[AccessD] Getting the start and end dates of the current week

Given that there were a few methods for returning a fixed end of week date, 
I rewrote a few of the procedures to speed up the procedures.  Mine was 
written several years ago during my first month of experimenting with VBA so 
that is my excuse for calling the Date function three times in a single line 
of code.  I started with a timeGetTime declare and ran a million calls to 
each revised procedure:

Public Declare Function timeGetTime Lib "Winmm" () As Long

Function EndsOfWeek() As Date
    Dim datLastOfWeek As Date

    datLastOfWeek = Date
    EndsOfWeek = datLastOfWeek + (7 - WeekDay(datLastOfWeek))
End Function

Function fnLastOfThisWeek() As Date
    Dim dt As Date
    dt = Date

    fnLastOfThisWeek = DateSerial(Year(dt), Month(dt), Day(dt) + 7 - 
WeekDay(dt))
End Function

Function WeekEndDate() As Date
    Dim dt As Date

    dt = Date
    WeekEndDate = DateAdd("d", 1 + WeekDay(dt), dt)
End Function

The test driver procedure was run by commenting out two of the three calls.  
Each was run in sequence several times.  The results are shown as a comment 
immediately after the call:

Sub timetest()
Dim dt As Date
Dim lng As Long
Dim lngt As Long
lngt = timeGetTime

For lng = 0 To 1000000
'dt = EndsOfWeek() '1.79
'dt = fnLastOfThisWeek '3.56
dt = WeekEndDate '4.37
Next
MsgBox timeGetTime - lngt
End Sub

Looks like DateAdd is slower than the DateSerial, but both can be skipped as 
demonstrated by Don Mcgillivray.  There is little to choose between the 
approaches on an occasional call, but used in a query with calculated field 
returning a few million records, I'd go with a variation on Don's method as 
it's nearly twice as fast as the next fastest.

As always, kudos to Gustav for providing an appropriately globalized 
approach.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail  
http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca