For a system that we developed, we needed to run a report which showed sales from Friday to Friday.  Initially we set the report up manually and ran the report each week by entering the dates.

This seemed prone to error and also limitted our ability to re-run the report for prior weeks.  The solutions we came up with was to create a table called “tblFridays”, populate with the date range that we wanted, join to the query and hey presto …

Rather than entering the dates manually, I thought a bit of VBA code would do the trick quite nicely.  What confused me for a while is how Access handles dates.  Essentially Access wants the dates in American format, regardless of what your local is set to.  Now this can get confusing as if you get access to convert the date object to a string for inserting in to your query, it converts it to your local date format!!!  So you need a little helper function …

Public Sub populateFridays()

'# Quick and dirty routine to populate tblFridays with
'# Friday dates between startdate and endDate.

Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
Dim db As Database
Dim sql As String

Set db = CurrentDb()

startDate = #1/2/2009#      '# Note US format.
endDate = #7/17/2009#       '# Note US format.

currentDate = startDate

Do While currentDate <= endDate

    sql = "insert into tblFridays values (#" & usDate(currentDate)
             & " 06:00:00#, #" & usDate(currentDate + 7)
             & " 05:59:59#)"

    db.Execute (sql)

    currentDate = currentDate + 7
Loop

End Sub

Public Function usDate(dateToConvert As Date) As String

    usDate = Month(dateToConvert) & "/"
                & Day(dateToConvert) & "/"
                & Year(dateToConvert)

End Function

Microsoft Access Table of "Fridays"