Microsoft Office 2000/Visual Basic Programmer's Guide

Assembling a Date

To work with a date in code, you sometimes need to break it down into its component parts—that is, its day, month, and year. Once you've done this, you can perform a calculation on one element, and then reassemble the date. To break a date into components, you can use the Day, Month, and Year functions. Each of these functions takes a date and returns the day, month, or year portion, respectively, as an Integer value. For example, Year(#2/23/98#) returns "1998."

To reassemble a date, you can use the DateSerial function. This function takes three integer arguments: a year, a month, and a day value. It returns a Date value that contains the reassembled date.

Often you can break apart a date, perform a calculation on it, and reassemble it all in one step. For example, to find the first day of the month, given any date, you can write a function similar to the following one:

Function FirstOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the first day of a month, given a date.
   ' If no date is passed in, the function uses the current date.
   
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If
   
   ' Find the first day of this month.
   FirstOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)
End Function

The FirstOfMonth procedure takes a date or, if the calling procedure doesn't pass one, uses the current date. It breaks the date into its component year and month, and then reassembles the date using 1 for the day argument. Calling this procedure with the dteDate argument #2/23/98# returns "2/1/98".

The following procedure uses the same strategy to return the last day of a month, given a date:

Function LastOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the last day of a month, given a date.
   ' If no date is passed in, the function uses the current date.
   
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If
   
   ' Find the first day of the next month, then subtract one day.
   LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

Both of these procedures are available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

VBA also provides functions that you can use to disassemble and reassemble a time value in the same manner. The Hour, Minute, and Second functions return portions of a time value; the TimeSerial function takes an hour, minute, and second value and returns a complete time value.