Force all dates to appear between begin and end dates

[RT_Indy]

I currently have a simple sales database that captures sales by date and
writes it to a table. I want to design a report that pulls sales on a
rolling 30 day basis. However, there are periodically days with no sales,
and I want sales on those days represented as a zero as opposed to being
omitted from the range of dates. See below example:

Current:
Date: Sales
09-01-08 5
09-02-08 2
09-04-08 7

Desired:
Date: Sales
09-01-08 5
09-02-08 2
09-03-08 0
09-04-08 7

Thanks in advance,
RT_Indy

 

[Ken Sheridan]

Create an auxiliary calendar table, which is simply a table of all dates.
There are various ways you can do this:

1. Serially fill down a column in Excel and import it into Access as a table.

2. Call a function which builds the table on the basis of start and end
dates passed into the function as arguments. I'll give an example at the end
of this reply.

You can then LEFT JOIN the calendar table to the sales table e.g. for the 30
days up to and including today:

SELECT calDate, NZ(sales,0)
FROM Calendar LEFT JOIN SalesTable
ON Calendar.calDate = SalesTable.saleDate
WHERE caldate BETWEEN DATE()-29 AND DATE();

BTW don't name a column Date. As you see from the above it’s the name of a
built in function so should be avoided.

Here's the procedure for creating a calendar table:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

As you see you can restrict it to certain days of the week if you wish, but
to include all days you'd call it like this to create a table covering 2005
to 2015 for instance:

MakeCalendar_DAO "Calendar", "01/01/2005#, #12/31/2015#, 0

Once you have the basic calendar table there are many other uses you can out
it to, e.g. you can add a Boolean (Yes/No) isHoliday column for public
holidays. This enables you to calculate working days between two dates for
instance:

PARAMETERS
[Start date:] DATETIME,
[End date:] DATETIME;
SELECT COUNT(*)
FROM Calendar
WHERE NOT isHoliday
AND WEEKDAY(calDate, 2) < 6
AND caldate BETWEEN
[Start date:] AND [End date:];

Ken Sheridan
Stafford, England

 

[Dale Fye]

Another way to get a list of dates, without making and maintaining a
CalendarDates table is to:
1. create a table (I'll call it tbl_Numbers) which contains a single field
(lngNumber) and 10 records (values 0 through 9).

2. create a query (I'll call it qry_Numbers) based on this table that
generates a series of numbers (this example will generate numbers from 0 to
999).

SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

3. Now create a query (qry_DateRange) to generate the dates you want. I
generally use textboxes on forms as my StartDate and EndDate references:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT [StartDate]+[lngNumber] AS SomeDate
FROM qry_Numbers
WHERE [StartDate]+[lngNumber]) Between [StartDate] And [EndDate]

Then, as Ken indicates you LEFT JOIN this query (as if it were the Calendar
table) to the Sales table

HTH
Dale

 

[RT_Indy]

Thanks to both of you for your replies based on your suggestions. I was able
to get it to work beautifully.

RT_Indy