"Convenience" Date Functions

Manipulating dates is a common operation in most Microsoft Access applications. For example, let's say I want to pre-filter a form's recordsource to show only the records from the current month.  Or, maybe I want to pre-fill the start and end dates for a report to January 1st of the current year and the previous month end.

The DateSerial function makes these operations straightforward:

'Calculate the start and end of the current month
StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date)+1, 0)

'Calculate the start of the year and end of the previous month
ReportStart = DateSerial(Year(Date), 1, 1)
ReportEnd = DateSerial(Year(Date), Month(Date), 0)

This is pretty basic stuff for most Access developers.  Which is why many Access developers are content to just use the code as written above.  

Here's the problem.  The above code is pretty easy to write.  But it's not that easy to read.  Mind you, it's not particularly difficult.  But it's not easy.  Without the comments above the statements, you need to stop and think about what each line is actually doing.  

And what happens when you decide that you want to pre-fill the ending report date with the current month end instead of the previous month?  What if you change the code but forget to change the comment?  I call that good comments going bad.

What's the solution?  As I wrote in the article linked above, the solution is to write self-documenting code whenever possible.  And the way we do that is to write code that's easy to read.

Compare the code sample above to this code, rewritten using some very basic date functions:

StartDate = MonthStart(Date)
EndDate = MonthEnd(Date)

ReportStart = YearStart(Date)
ReportEnd = MonthEnd(Date, -1)

The code above reads almost like plain English sentences.  That's the goal.

Here's a list of the basic date functions I use to improve my code readability.  I refer to these kinds of functions as convenience functions.  A convenience function is a one- or two-line function that wraps up some basic functionality and gives it an easy-to-read name.

Function IsEndOfMonth(DateToCheck As Date) As Boolean
    IsEndOfMonth = (DateToCheck = DateSerial(Year(DateToCheck), Month(DateToCheck) + 1, 0))
End Function

Function IsEndOfYear(DateToCheck As Date) As Boolean
    IsEndOfYear = (Month(DateToCheck) = 12 And Day(DateToCheck) = 31)
End Function

Function IsEndOfQuarter(DateToCheck As Date) As Boolean
    IsEndOfQuarter = (IsEndOfMonth(DateToCheck) And Month(DateToCheck) Mod 3 = 0)
End Function

'Returns 1/1/
Function YearStart(AsOfDate As Date, Optional RelativeYear As Integer = 0) As Date
    YearStart = DateSerial(Year(AsOfDate) + RelativeYear, 1, 1)
End Function

'Returns 12/31/
Function YearEnd(AsOfDate As Date, Optional RelativeYear As Integer = 0) As Date
    YearEnd = DateSerial(Year(AsOfDate) + RelativeYear, 12, 31)
End Function

'Returns 1/1/, 2/1/, 3/1/, 4/1/, 5/1/, 6/1/, 7/1/, 8/1/, 9/1/, 10/1/, 11/1, 12/1
Function MonthStart(AsOfDate As Date, Optional RelativeMonth As Integer = 0) As Date
    MonthStart = DateSerial(Year(AsOfDate), Month(AsOfDate) + RelativeMonth, 1)
End Function

'Returns 1/31/, 2/28/ or 2/29/, 3/31/, 4/30/, 5/31/, 6/30/, 7/31/, 8/31/, 9/30/, 10/31/, 11/30/, 12/31/
Function MonthEnd(AsOfDate As Date, Optional RelativeMonth As Integer = 0) As Date
    MonthEnd = DateSerial(Year(AsOfDate), Month(AsOfDate) + RelativeMonth + 1, 0)
End Function

'Returns 1/1/, 4/1/, 7/1/, 10/1/
Function QuarterStart(AsOfDate As Date, _
                      Optional RelativeQuarter As Integer = 0, _
                      Optional MonthOfQuarter As Integer = 1) As Date
Dim Mo As Integer
    Mo = Int((Month(AsOfDate) + 2 + (3 * RelativeQuarter)) / 3) * 3
    Mo = Mo - 3 + MonthOfQuarter
    QuarterStart = DateSerial(Year(AsOfDate), Mo, 1)
End Function

'Returns 3/31/, 6/30/, 9/30/, 12/31/
Function QuarterEnd(AsOfDate As Date, _
                    Optional RelativeQuarter As Integer = 0, _
                    Optional MonthOfQuarter As Integer = 3) As Date
Dim Mo As Integer
    Mo = Int((Month(AsOfDate) + 2 + (3 * RelativeQuarter)) / 3) * 3
    Mo = Mo - 3 + MonthOfQuarter + 1
    QuarterEnd = DateSerial(Year(AsOfDate), Mo, 0)
End Function

Image by Amber Avalona from Pixabay