How to Find the First and Last Day in a Given Week, Month, or Year in Access 2010

In this article, you learn the steps of using Visual Basic for Applications (VBA) to create functions that return the first and last day in a given week, month, or year. You can specify a date for the calculation, or you can pass no parameter to indicate the current date.

The date functions built into VBA make it easy to perform calculations based on date arithmetic. Whether you must calculate the first or last day in a given week, month, or year, you can easily get the value that you want.

Adding the Sample Code

To use the sample code provided with this article in your own applications, you must start by either adding a new module to your database, or finding an existing module into which to place the code. These procedures should be put in a standard module, not a class module, to get the most use from them. To create a module, in Access 2010, select the Create command, and then in the Macros & Code group, select Module. This action opens the Visual Basic Editor with the new module selected. To use an existing module, in the Macros & Code group, select Visual Basic to load the Visual Basic Editor, and then select the module. Either way, copy the code from the sample database into the selected module. If you prefer, you can import only the module, DateTimeCode, from the sample database into your own database.

Using the Sample Code

The sample module includes six functions:

• FirstDayInMonth

• LastDayInMonth

• FirstDayInYear

• LastDayInYear

• FirstDayInWeek

• LastDayInWeek

Each function accepts an optional parameter indicating the reference date for the function. If you call any of the functions without passing a parameter, the function uses the current date as the reference date.

Using either the sample database or your own database that has the code inserted, you can interact with the functions by using the Immediate window. Press Ctrl+G to display the Immediate window, and type the following lines. Press Enter at the end of each line to display the function results in the Immediate window.

? FirstDayInMonth(#2/15/2012#)
? LastDayInMonth(#2/15/2012#)
? FirstDayInWeek
? LastDayInWeek
? FirstDayInYear(Date)
? LastDayInYear(Date)

The first two functions return #2/1/2012# and #2/29/2012#. The second two functions return the first and last day in the current week (using the definition of the first day of the week in the current locale). The final two functions return the first and last day in the current year.

To test the functions in a query, first create a table. Name the table DateTable, with a single Date column named TestDate. Add several dates to the column. Next, create a query; in SQL view, add the following code.

SELECT DateTable.TestDate, 
  FirstDayInMonth([TestDate]) AS FirstDayInMonth, 
  LastDayInMonth([TestDate]) AS LastDayInMonth, 
  FirstDayInYear([TestDate]) AS FirstDayInYear, 
  LastDayInYear([TestDate]) AS LastDayInYear, 
  FirstDayInWeek([TestDate]) AS FirstDayInWeek, 
  LastDayInWeek([TestDate]) AS LastDayInWeek
FROM DateTable;

Run the query, and you can see that it called each of the sample functions, passing the date in the TestDate column.

Examining the Sample Code

Each of the procedures works in the same manner. Start by examining the FirstDayInMonth function; the following paragraphs describe each of the remaining functions.

The FirstDayInMonth procedure resembles the following (with its introductory comments removed).

Public Function FirstDayInMonth(Optional dtmDate As Variant) As Date
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    FirstDayInMonth = DateSerial( _
     Year(dtmDate), Month(dtmDate), 1)
End Function

The code starts by accepting an optional Variant parameter that contains the reference date for the function. Because the parameter is optional, you can call the function without supplying a parameter value—the function supplies a value if you do not.

The code within the procedure starts by calling the IsMissing function to determine whether you passed a parameter. The function returns True if you neglected to supply a date. If the parameter is missing, the code puts the current date into the parameter value. The VBA Date function supplies this value. Each of the six sample functions follows this pattern.

Next, the function uses the DateSerial function to construct the return value, the first day in the month for the supplied date. The DateSerial function accepts three parameters: integers representing the year, month, and day pieces of a date. The function returns the date that corresponds to the three integers you supply. In this case, the code extracts the year and month pieces of the date that you supplied (by using the VBA Year and Month functions), and uses 1 for the day portion, representing the first day in the month. The function, just as all the other sample functions, assigns the value to the name of the function to return a value from the function.

FirstDayInMonth = DateSerial( _
 Year(dtmDate), Month(dtmDate), 1)

The LastDayInMonth function does basically the same work, except it uses the fact that the DateSerial function understands positive, negative, and zero values for the day. Because using a value of 1 in the day portion returns a date that corresponds to the first day of a month, using 0 logically returns a date that corresponds to the day before the first day of a month—the last day of the previous month.

Therefore the LastDayInMonth function calls the DateSerial function again. But this time it uses a value that corresponds to the next month, but day 0. This returns the day before the first day of the next month, or the last day in the current month.

LastDayInMonth = DateSerial( _
 Year(dtmDate), Month(dtmDate) + 1, 0)

The FirstDayInWeek function uses a different approach. It relies on two important date-handling techniques in VBA:

• Dates are stored internally as serial floating-point values, with the whole-number portion representing the number of days since 12/30/1899, and the fractional portion representing the fraction of a day since midnight. For example, the date 12/31/1899 at noon is represented by the value 1.5, and the date 1/1/2012 at midnight is represented by 40909, the number of days since 12/30/1899. (If you really want some fun, do some searching to understand why VBA treats 12/30/1899 instead of 12/31/1899 as day 0. You'll be amused by the results.) Storing dates as floating-point values lets you write code that adds or subtracts days from a date by adding or subtracting integers from a date value.

• The Weekday function returns a number that corresponds to the day of the week associated with a given date. The function, by default, respects the local culture. (Various cultures consider various days as the first day of the week). In the United States, Sunday is treated as the first day of the week, so the Weekday function returns 1 for Sunday dates.

The FirstDayInWeek function relies on these facts to calculate a date that corresponds to the first day of the week.

FirstDayInWeek = dtmDate - _
 Weekday(dtmDate, vbUseSystemDayOfWeek) + 1

This code calculates the day of the week that corresponds to the supplied date, and subtracts that number from the supplied date. If the supplied date is Wednesday, for example, and the code expects that Sunday is the first day of the week, the Weekday function returns 4, and subtracting 4 from the supplied date returns the date of the previous Saturday. Add 1 to the result, and you end up on the Sunday before the supplied date.

The LastDayInWeek function uses similar code, but it adds 7 to the modified date. In the previous example, subtracting the result of calling the Weekday function returns the Saturday before the supplied date. Adding 7 moves to the Saturday after the supplied date.

LastDayInWeek = dtmDate - _
 Weekday(dtmDate, vbUseSystemDayOfWeek) + 7

The FirstDayInYear and LastDayInYear functions are simple, given the functionality of the DateSerial function. The FirstDayInYear function uses the year piece of the supplied date, and uses 1 for both the month and day portions:

FirstDayInYear = DateSerial(Year(dtmDate), 1, 1)

The LastDayInYear function again uses the year piece of the supplied date, but 12 and 31 as the month and day portions:

LastDayInYear = DateSerial(Year(dtmDate), 12, 31)

The functions described here work not only in Access but also in any modern VBA host product. This includes the products in Microsoft Office 2003 through Office 2010.

You might wonder why the parameter for each function is defined as a Variant when, in fact, the value must be a date. The reasoning comes from the design of the IsMissing function, which works with only Variant values. If you specify the parameter to each function as a Date value, the IsMissing function always returns False. Calling each function without a parameter then fails because the IsMissing function was unable to determine whether you passed a parameter value.

We recommend taking the time to examine all the VBA date and time functions. They are surprisingly flexible and handle many situations you might not expect (as in the preceding LastDayInMonth example).

Length: 09:44

Grab the Code

• Access Developer Center

• Office Developer Center

• Microsoft Access Team Blog

About the Author

Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).