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

Hi Jürgen

Hey, nothing has changed - Jürgen is still haunting the last
microsecond!

But you are right, of course - every second counts when retrieving and
manipulation a million records.

However, in most cases functions like these are used for data entry
validation, report grouping and the like where a microsecond or ten is
as close to "nothing" as it doesn't matter - it is more important to
have a selection of proved functions in your toolbox you can pick when
needed knowing that they'll do the job without further testing. That's
why I have made it a habit to use and recommend the built in date/time
functions which actually are quite fast and won't let you down (except
for an ISO week number bug I have documented previously). Also, it is
often easier to document or comment what a function does when you use
the date/time functions.

Then you can concentrate on speed optimizing for large recordset using
SQL-only code in the few cases where it is worth the trouble and
testing.

/gustav


> 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