Count days of a certain month between date fields

[eileenjess]

I have a form with two fields: beginningdate and endingdate which record when
clients check-in and check-out of our facility. I would like to make a
query/report that brings up any records with dates falling in a certain month
(even if beginingdate and endingdate are not in those months). I also would
like to total the number of bednights a client stayed in a particular month
(one total number). For instance, if a client stayed from 2/26/06 - 3/5/06,
I would like the query/report to return 4 days for March and 3 days for
February. I know I would use the Between function, but I'm not sure how to
distinguish between months. Thanks in advance for any help!

 

[Ken Sheridan]

To determine if any part of the date range falls within the year and month in
question try this:

SELECT *
FROM YourTable
WHERE (YEAR(beginningdate ) = [Enter Year:]
AND MONTH(beginningdate ) = [Enter Month:])
OR (YEAR(endingdate) = [Enter Year:]
AND MONTH(endingdate) = [Enter Month:])
OR (DATESERIAL([Enter Year:],EnterMonth:],1)
BETWEEN beginningdate AND endingdate)
OR (DATESERIAL([Enter Year:],EnterMonth:]+1,0)
BETWEEN beginningdate AND endingdate);

The month should be entered as a number at the parameter prompt, 1 for
January, 2 for February etc. The query looks at four possible scenarios:

1. The beginningdate is in the year and month in question.
2. The endingdate is in the year and month in question.
3. The first day of the month in question falls between the beginning and
ending dates.
4. The last day of the month in question falls between the beginning and
ending dates.

If any of these are true then the row will be returned.

To count the number of occupancy nights grouped by month the trick is to
first create a Calendar table, which is simply a table of all dates over a
given period, 10 years say, in a column caldate. An easy way to create the
table is to serially fill down a column in Excel with the dates and import
the worksheet into Access as a table. It can also be done completely within
Access itself with some VBA code which I can post here if you wish.

You can then join your table to the Calendar table in a query like so:

SELECT
ClientID,
YEAR(caldate) AS YearOccupied,
MONTH(caldate) As MonthOccupied,
COUNT(*) AS NightsOccupied
FROM YourTable, Calendar
WHERE caldate BETWEEN beginningdate AND endingdate-1
GROUP BY ClientID, YEAR(calDate), MONTH(calDate);

Because the endingdate is not a bednight, the last bednight in your example
being 4 March, one is subtracted from the endingdate when defining the range
for the join criterion in the query's WHERE clause. If you are wondering why
the join is done in the WHERE clause rather than a JOIN clause its because
the BETWEEN….AND operator does not work in a JOIN clause. The query could be
written with a JOIN clause but it would be necessary to use >= and <=
operations, which will work in a JOIN clause.

BTW its very important when working with date ranges in this way that the
date/time values in the table do not have a non-zero time of day. If the
dates have been entered manually as dates this will be the case, but
sometimes people mistakenly use the Now() function to automatically insert a
date into a table, which results in a date with the current time of day being
entered, which may not be readily apparent if the values are formatted as a
date. To automatically insert a date into a table the Date() function should
be used. This inserts a date with a zero time of day, there being no such
thing in Access a date without a time of day or vice versa when the date/time
data type is used.

I haven't tested the two queries, but I think I've got the logic right.

Ken Sheridan
Stafford, England

 

[eileenjess]

Thank you so much for your quick response, Ken. I created the query with
your advice and it worked perfectly! However, I am confused about how to
create the calander table to count the bednights. I would like to create do
this entirely in access. Would you help me with this? Once again, thanks for
your help.

 

[Ken Sheridan]

First paste the following function into any standard module in the database.
The function allows you to create a calendar table of whatever name you wish
and to select which days of the week you want included. In your case you'd
presumably want them all so to create a table named Calendar running from 1
January 2006 to 31 January 2015 say you'd call it like so (note that the
literal dates must be in US format).

MakeCalendar "Calendar", #01/01/2006#, #12/31/2015#, 0

You can enter this in the debug window in Access (AKA the immediate pane)
which you can open by pressing Ctrl-G while in Access. The function will
take a few moments to create the table, so be patient.

The function references the Microsoft ADO Extensions for DDL and Security
object library in addition to the main Microsoft ActiveX Data Objects
library, so you'll also need to create a reference to the former as well as
the latter. You do this from the Tools|Refernces menu on the VBA menu bar
(the one you see while you have the module open). Just scroll down the list
of references until you find it and then tick the check box.

Here's the code for the function:

Public Function MakeCalendar(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 cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

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

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

' refresh database window
Application.RefreshDatabaseWindow

' refresh catalog
cat.Tables.Refresh

' set validation rule for caldate column to prevent
' insertion of rows outside data range
Set tbl = cat(strTable)
tbl.Columns("caldate").Properties("Jet OLEDB:Column Validation Rule") = _
"BETWEEN #" & Format(dtmStart, "mm/dd/yyyy") & "# AND #" & _
Format(dtmEnd, "mm/dd/yyyy") & "#"


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

cmd.CommandText = strSQL
cmd.Execute
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
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function

Ken Sheridan
Stafford, England

 

[eileenjess]

Thank you, Ken! That worked perfectly. Thanks for taking the time to help
me with my problem.