Data Entry and Date/Time Values

To programmatically create a column that would hold date/time values using the Microsoft Access Object Library, set its data type to DB_DATE. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDateHired As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT, 80)
    tblEmployees.Fields.Append colFullName
    Set colDateHired = tblEmployees.CreateField("DateHired", DB_DATE)
    tblEmployees.Fields.Append colDateHired
    
    curDatabase.TableDefs.Append tblEmployees
    
    MsgBox "A table named Employees has been created."
End Sub

You can also use the dbDate data type.

If you are creating the column using DAO, set its data type to dbDate. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As DAO.Database
    Dim tblContractors As DAO.TableDef
    Dim fldName As DAO.Field
    Dim fldStartDate As DAO.Field
    Dim fldEndDate As DAO.Field

    Set curDatabase = CurrentDb
    Set tblContractors = curDatabase.CreateTableDef("Contractors")
    
    Set fldName = tblContractors.CreateField("Contractor Name", DB_TEXT, 100)
    tblContractors.Fields.Append fldName
    Set fldStartDate = tblContractors.CreateField("Start Date", dbDate)
    tblContractors.Fields.Append fldStartDate
    Set fldEndDate = tblContractors.CreateField("End Date", dbDate)
    tblContractors.Fields.Append fldEndDate
    
    curDatabase.TableDefs.Append tblContractors
    
    MsgBox "A table named Contractors has been created."
End Sub

You can also use the DB_DATE data type.

If you are creating the table using a SQL statement, set its column's data type to either the DATE or the DATETIME types. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "DateHired Date, " & _
                 "DateLastReviewed DateTime);"
                 
    MsgBox "A table named Employees has been created."
End Sub

Both data types have the same effect in Microsoft Access.

To programmatically perform data entry on a table using the Microsoft Access Object library, if the column was created for a date or a time data type, you must use an appropriate formula with the year represented by 2 or 4 digits. If you want to specify the year with 2 digits, use the formula:

mm/dd/yyyy

or

mm-dd-yy

If you want to use a 4-year digit, apply the following formula:

mm/dd/yyyy

or

mm-dd-yyyy

The year with 4 digits is more precise as it properly expresses a complete year. A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic. Here are examples:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colStartVacationDate As Object
    Dim colEndVacationDate As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("Full Name", DB_TEXT, 80)
    tblEmployees.Fields.Append colFullName
    Set colStartVacationDate = tblEmployees.CreateField("Start Vacation Date", DB_DATE)
    tblEmployees.Fields.Append colStartVacationDate
    Set colEndVacationDate = tblEmployees.CreateField("End Vacation Date", DB_DATE)
    tblEmployees.Fields.Append colEndVacationDate
    
    curDatabase.TableDefs.Append tblEmployees
    
    MsgBox "A table named Employees has been created."
End Sub

Private Sub cmdCreateRecord_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("Full Name").Value = "Helene Mukoko"
    rstEmployees("Start Vacation Date").Value = "04/26/2009"
    rstEmployees("End Vacation Date").Value = "04-26-2009"
    rstEmployees.Update
    
    MsgBox "A record has been added to the Employees table."
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

To perform data entry of date or time fields in DAO, follow the same rules as for the Microsoft Access Object library.

In the SQL, to perform data entry on a date or time field, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date between # and #. If you want to specify the year with 2 digits, use the formula:

#mm-dd-yy#

Or

#mm/dd/yy#

You can use the dash symbol "-" or the forward slash "/" as the date separator. The year, the month, and the day can each be specified with a single digit. Here is an example:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES('Annette Schwartz', #22-10-09#, #11/22/09#)"
    MsgBox "A record has been added to the Employees table."
End Sub

When the year is specified with 1 digit, its number is added to the current decade. For example, a year with 6 is represented as 2006. The 1-year digit formula is suitable for a date that occurs in the current decade. As you may guess, it is better to represent a date with at least two digits, including a leading 0. The 2-year digit formula is suitable for a date that occurs in the current century.

An alternative to representing a year is with 4 digits. In this case, you would use the formulas:

#yyyy-mm-dd#

Or

#yyyy/mm/dd#

Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES('Mark Drowns', " & _
		 "#2009-06-02#, #2009/06/28#)"
    MsgBox "A record has been added to the Employees table."
End Sub

When performing data entry in the SQL, you can also include a date or time value in single-quotes. Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Spencer Harland', '03-08-09', '03/28/09')"
    MsgBox "A record has been added to the Employees table."
    
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Kevin Sealans', '2009-07-20', '2009/08/18')"
    MsgBox "A record has been added to the Employees table."
End Sub

You can also include the 4-year digit as the last part of the value. Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Anselme Bows', #09-13-2009#, '10/10/2009')"
    MsgBox "A record has been added to the Employees table."
    
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Jeremy Huissey', '07-20-2009', #08/18/2009#)"
    MsgBox "A record has been added to the Employees table."
End Sub

In Lesson 3, we saw that the Visual Basic language has a strong support for date values starting with a data type named Date. We saw that, to declare a date variable, you use the Date data type. To initialize date or time variable, you must include its value between two # signs but following the rules of a date format from the Regional Settings of Control Panel. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = #2/8/2003#

    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

In US English, to express a date value, you can use one of the following formats:

• mm-dd-yy
• mm-dd-yyyy

You must start the date with a number that represents the month (a number from 1 to 12). After the month value, enter -. Then type the day value as a number between 1 and 28, 29, 30, or 31 depending on the month and the (leap) year. Follow it with -. End the value with a year in 2 or 4 digits. Here are examples 06-12-08 or 10-08-2006.

You can also use one of the following formats:

• dd-mmm-yy
• dd mmm yy
• dd-mmmm-yy
• dd mmmm yy
• dd-mmm-yyyy
• dd mmm yyyy
• dd-mmmm-yyyy
• dd mmmm yyyy

This time, enter the day value followed either by an empty space or -. Follow with the short name of the month in the mmm placeholder or the complete name of the month for the mmmm placeholder, followed by either an empty space or -. End the value with the year, using 2 or 4 digits.

As you may know already, in US English, you can start a date with the month. In this case, you can use one of the following formats:

• mmm dd, yy
• mmm dd, yyyy
• mmmm dd, yy
• mmmm dd, yyyy

As seen with the previous formats, mmm represents the short name of a month and mmmm represents the full name of a month. As mentioned already, the dd day can be expressed with 1 or 2 digits and the single digit can have a leading 0. After the day value, (you must) enter a comma followed by the year either with 2 or 4 digits.

When you compose a date value, you must follow some rules. The rules depend on the language you are using. We will review those of the US English.

In a year, a month is recognized by an index in a range from 1 to 12. A month also has a name. The name of a month is given in two formats: complete or short. These are:

A week is a combination of 7 consecutive days of a month. Each day can be recognized by an index from 1 to 7 (1, 2, 3, 4, 5, 6, 7). The day of each index is recognized by a name. In US English, the first day has an index of 1 is named Sunday while the last day with an index of 7 is named Monday. Like the months of a year, the days of a week have long and short names. These are:

These are the default in US English. In most calculations, the Visual Basic language allows you to specify what day should be the first in a week.

The year is expressed as a numeric value.

If you have a value such as one provided as a string and you want to convert it to a date, you can call the CDate() function. Its syntax is:

Function CDate(Value As Variant) As Date

This function can take any type of value but the value must be convertible to a valid date. If the function succeeds in the conversion, it produces a Date value. If the conversion fails, it produces an error.

We have seen that, when creating a date, you can include its value between # signs. An alternative is to provide a date as a string. To support this, the Visual Basic language provides a function named DateValue. Its syntax is:

Function DateValue(ByVal StringDate As String) As Variant

When calling this function, provide a valid date as argument. The validity depends on the language of the operating system. If working in US English, you can use one of the formats we saw above. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = DateValue("22-Aug-2006")
        
    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

An alternative to initializing a date variable is to use a function named DateSerial. Its syntax is:

Function DateSerial(ByVal [Year] As Integer, _
   		    ByVal [Month] As Integer, _
   		    ByVal [Day] As Integer) As Variant

As you can see, this function allows you to specify the year, the month, and the day of a date value, of course without the # signs. When it has been called, this function returns a Variant value, which can be converted into a Date. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = DateSerial(2003, 2, 8)
    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

When passing the values to this function, you must restrict each component to the allowable range of values. You can pass the year with two digits from 0 to 99. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = DateSerial(3, 2, 8)
    MsgBox "Date Hired: " & DateHired
End Sub

If you pass the year as a value between 0 and 99, the interpreter would refer to the clock on the computer to get the century. At the time of this writing (in 2009), the century would be 20 and the specified year would be added, which would produce 2003. To be more precise and reduce any confusion, you should always pass the year with 4 digits.

The month should (must) be a value between 1 and 12. If you pass a value higher than 12, the interpreter would calculate the remainder of that number by 12 (that number MOD 12 = ?). The result of the integer division would be used as the number of years and added to the first argument. The remainder would be used as the month of the date value. For example, if you pass the month as 18, the integer division would produce 1, so 1 year would be added to the first argument. The remainder is 6 (18 MOD 12 = 6); so the month would be used as 6 (June). Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = DateSerial(2003, 18, 8)
    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

As another example, if you pass the month as 226, the integer division (226 \ 12) produces 18 and that number would be added to the first argument (2003 + 18 = 2021). The remainder of 226 to 12 (226 MOD 12 = 10) is 10 and that would be used as the month. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = DateSerial(2003, 226, 8)
    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

If the month is passed as 0, it is considered 12 (December) of the previous year. If the month is passed as -1, it is considered 11 (November) of the previous year and so on. If the month is passed as a number lower than -11, the interpreter would calculate its integer division to 12, add 1 to that result, use that number as the year, calculate the remainder to 12, and use that result as the month.

Depending on the month, the value of the day argument can be passed as a number between 1 and 28, between 1 and 29, between 1 and 30, or between 1 and 31. If the day argument is passed as a number lower than 1 or higher than 31, the interpreter uses the first day of the month passed as the second argument. This is 1.

If the day is passed as -1, the day is considered the last day of the previous month of the Month argument. For example, if the Month argument is passed as 4 (April) and the Day argument is passed as -1, the interpreter would use 31 as the day because the last day of March is 31.

If the Month argument is passed as 3 (March) and the Day argument is passed as -1, the interpreter would refer to the Year argument to determine whether the year is leap or not. This would allow the interpreter to use either 28 or 29 for the day value. The interpreter uses this algorithm for any day value passed as the third argument when the number is lower than 1.

If the Day argument is passed with a value higher than 28, 29, 30, or 31, the interpreter uses this same algorithm in reverse order to determine the month and the day.

As seen so far, a date is a value made of at least three parts: the year, the month, and the day. The order of these components and how they are put together to constitute a recognizable date depend on the language and they are defined in the Language and Regional Settings in Control Panel.

The Visual Basic language supports the year of a date ranging from 1 to 9999. This means that this is the range you can consider when dealing with dates in your worksheets. In most operations, when creating a date, if you specify a value between 1 and 99, the interpreter would use the current century for the left two digits. This means that, at the time of this writing (2009), a year such as 4 or 04 would result in the year 2004. In most cases, to be more precise, you should usually or always specify the year with 4 digits.

If you have a date value whose year you want to find out, you can call the Visual Basic's Year() function. Its syntax is:

Public Function Year(ByVal DateValue As Variant) As Integer

As you can see, this function takes a date value as argument. The argument should hold a valid date. If it does, the function returns the numerical year of a date. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date
    
    DateHired = #2/8/2004#
    MsgBox "In the job since " & Year(DateHired)
End Sub

This would produce:

The month part of a date is a numeric value that goes from 1 to 12. When creating a date, you can specify it with 1 or 2 digits. If the month is between 1 and 9 included, you can precede it with a leading 0.

If you have a date value and want to get its month, you can call the Month() function. Its syntax is:

Function Month(ByVal DateValue As Variant) As Integer

This function takes a Date object as argument. If the date is valid, the function returns a number between 1 and 12 for the month. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date
    
    DateHired = #2/8/2004#
    MsgBox "Month hired " & Month(DateHired)
End Sub

This would produce:

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Exercise2 database

3. On the Ribbon, click Database Tools, in the Macro section, click Visual Basic

4. On the main menu of Microsoft Visual Basic, click View -> Immediate Window

5. In the Immediate window, type ?Date and press Enter

6. Notice that the system date is displayed

7. Still in the Immediate window, type ?Time and press Enter

8. Again, in the Immediate window, type ?Now and press Enter

As mentioned already, the Month() function produces a numeric value that represents the month of a date. Instead of getting the numeric index of the month of a date, if you want to get the name of the month, you can call the Visual Basic function named MonthName. Its syntax is:

Function MonthName(ByVal Month As Integer, _
		   Optional ByVal Abbreviate As Boolean = False) As String

This function takes one required and one optional arguments. The required argument must represent the value of a month. If it is valid, this function returns the corresponding name. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = #2/8/2004#
    MsgBox "Day hired " & MonthName(Month(DateHired))
End Sub

This would produce:

The second argument allows you to specify whether you want to get the complete or the short name. The default is the complete name, in which case the default value of the argument is False. If you want to get the short name, pass the second argument as True. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = #2/8/2004#

    MsgBox "Month hired " & MonthName(Month(DateHired), True)
End Sub

This would produce:

The day is a numeric value in a month. Depending on the month (and the year), its value can range from 1 to 29 (February in a leap year), from 1 to 28 (February in a non-leap year), from 1 to 31 (January, March, May, July, August, October, and December), or from 1 to 30 (April, June, September, and November).

If you have a date value and you want to know its day in a year, you can call the Day() function. Its syntax is:

Function Day(ByVal DateValue As Variant) As Integer

This function takes a date as argument. If the date is valid, the function returns the numeric day in the month of the date argument. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = #2/8/2004#
    MsgBox "Day hired " & Day(DateHired)
End Sub

This would produce:

1. In the Immediate window, type ?Day(#12/14/00#) and press Enter
2. Notice that the result is 14 as the numeral day of that date
3. Still in the Immediate window, type ?Day(Date()) and press Enter
4. Notice that the result is the numeric day of the system date
5. In the Immediate window, type ?Day(Date()+2) and press Enter
   This would display the numeral of 2 days from today
6. In the Immediate window, type ?Month("June 12, 1990") and press Enter
7. Notice that the result in 6, because June represents the 6th month of the year
8. In the Immediate window, type ?Month(#02/25/90#) and press Enter
   This produces 2 since February is the 2nd day of the year
9. In the Immediate window, type ?Year(Date()) and press Enter
10. Notice that this displays the current year of the system date.

To get the name of the day of a week, you can a function named WeekdayName. Its syntax is:

Function WeekdayName( _
   ByVal Weekday As Integer, _
   Optional ByVal Abbreviate As Boolean = False, _
   Optional ByVal FirstDayOfWeekValue As Integer = 0 _
) As String

This function takes one required and two optional arguments. The required argument must be, or represent, a value between 0 and 7. If you pass it as 0, the interpreter will refer to the operating system's language to determine the first day of the week, which in US English is Sunday. Otherwise, if you pass one of the indexes we saw above, the function would return the corresponding name of the day. Here is an example:

Private Sub cmdDateTime_Click()
    MsgBox "Day hired: " & WeekdayName(4)
End Sub

This would produce:

If you pass a negative value or a value higher than 7, you would receive an error.

The second argument allows you to specify whether you want to get the complete or the short name. The default value of this argument is False, which produces a complete name. If you want a short name, pass the second argument as True. Here is an example:

Private Sub cmdDateTime_Click()
    MsgBox "Day hired: " & WeekdayName(4, True)
End Sub

As mentioned already, the Visual Basic language allows you to specify what days should be the first day of the week. This is the role of the third argument.

Microsoft Access and the Microsoft Visual Basic language are equipped with various functions used to manipulate date and time values. At the most basic level, you can use the Date() function to get the system date of the computer. To display the system date in a text box, you can enter =Date() in its Control Source property.

To find out whether an expression holds a valid date, a valid, or not, you can call the IsDate() function. Its syntax is:

Public Function IsDate(ByVal Expression As Variant) As Boolean

This function takes an argument as the expression to be evaluated. If the argument holds a valid date and/or time, the function returns True. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Variant

    DateHired = "9/16/2001"
    MsgBox "Is 9/16/2001 a valid date? " & IsDate(DateHired)
End Sub

This would produce:

If the value of the argument cannot be evaluated to a valid date or time, the function returns False. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Variant

    DateHired = "Who Knows?"
    MsgBox "Is it a valid date? " & IsDate(DateHired)
End Sub

Formatting a date consists of specifying how the value would be displayed to the user. The Visual Basic language provides various options. The US English language supports two primary date formats known as long date and short date. You can check them in the Date property page of the Customize Regional Options accessible from the Regional Settings in Control Panel:

To support these primary formats, the Visual Basic language provides a function named FormatDateTime. Its syntax is:

Function FormatDateTime(
   ByVal Expression As Variant,
   Optional ByVal NamedFormat As Integer = 0
) As String

The first argument of this function must be a valid Date value. The second argument is an integer. For a date, this argument can be 1 or 2. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired$

    DateHired$ = FormatDateTime("22-Aug-2006", 1)
    MsgBox "Date Hired: " & DateHired
End Sub

This would produce:

To support more options, the Visual Basic language provides the Format() function that we saw in the previous lesson. We saw that its syntax was:

Function Format( _
   ByVal Expression As Object, _
   Optional ByVal Style As String = "" _
) As String

Remember that the first argument is the date that needs to be formatted. The second argument is a string that contains the formatting to apply. To create it, you use a combination of the month, day, and/or year characters we saw as date formats. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DateHired As Date

    DateHired = #12/28/2006#
    MsgBox "Date Hired: " & Format(DateHired, "MMMM dd, yyyy")
End Sub

This would produce:

The Visual Basic language supports time values. To create a time value, you can declare a variable of type Date. To initialize the variable, create a valid value using the rules specified in the Regional and language Settings of Control Panel, and include that value between two # signs. Here is an example;

Private Sub cmdDateTime_Click()
    Dim DepositTime As Date

    DepositTime = #7:14:00 AM#
    MsgBox "Deposit Time: " & DepositTime
End Sub

This would produce:

To get the current time of the computer, you can call the Time() function of the Visual Basic language. Here is an example:

Private Sub cmdDateTime_Click()
    MsgBox Time
End Sub

To get a combination of the date and the time of the computer, you can call a function named Now. Here is an example:

Private Sub cmdDateTime_Click()
    MsgBox Now
End Sub

Instead of including the time in # signs, you can also provide it as a string. To support this, the Visual Basic language provides a function named TimeValue. Its syntax is:

Function TimeValue(ByVal StringTime As String) As Variant

This function expects a valid time as argument. If that argument is valid, the function returns a time value. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DepositTime As Date

    DepositTime = TimeValue("7:14")
    MsgBox "Deposit Time: " & DepositTime
End Sub

As an alternative to initializing a time variable, you can call a function named TimeSerial. Its syntax is:

Function TimeSerial(ByVal Hour As Integer, _
		    ByVal Minute As Integer, _
   		    ByVal Second As Integer) As Variant

This function allows you to specify the hour, the minute, and the second values of a time. If you pass valid values, the function returns a time. Here is an example:

Private Sub cmdDateTime_Click()
    Dim DepositTime As Date

    DepositTime = TimeSerial(7, 14, 0)
    MsgBox "Deposit Time: " & DepositTime
End Sub

In US English, a time is made of various parts. The first of them is the hour. The time is a 24th spatial division of a day. It is represented by a numeric value between 0 and 23. When creating a time value, you specify the hour on the left side. To get the hour of a valid time, you can call a function named Hour. Its syntax is:

Function Hour(ByVal TimeValue As Variant) As Integer

This function takes a time value as argument. If a valid time is passed, the function returns the hour part.

An hour is divided in 60 parts. Each part is called a minute and is represented by a numeric value between 0 and 59. If you have a time value and want to get its minute part, you can call a function named Minute. Its syntax is:

Function Minute(ByVal TimeValue As Variant) As Integer

When calling this function, pass it a time value. If the argument holds a valid value, the function returns a number between 0 and 59 and that represents the minutes.

A minute is divided in 60 parts and each part is called a second. It is represented by a numeric value between 0 and 59. If you have a time value and want to extract a second part from it, you can call the Second() function named . Its syntax is:

Public Function Second(ByVal TimeValue As Variant) As Integer

If you call this function, pass a valid time. If so, the function would return a number represents the seconds part.

Because dates and times are primarily considered as normal values, there are various operations you can perform on them. You can add or subtract a number of years or add or subtract a number of months, etc. The Visual Basic language provides its own mechanisms for performing such operations thanks to its vast library of functions.

To support the addition of a value to a date or a time, the Visual Basic language provides a function named DateAdd. Its syntax is:

Function DateAdd( _
   ByVal Interval As String, _
   ByVal Number As Double, _
   ByVal DateValue As Object _
) As Variant

This function takes three arguments that all are required.

The DateValue argument is the date or time value on which you want to perform this operation. It must be a valid Date value.

The Interval argument is passed as a string. It specifies the kind of value you want to add. This argument will be enclosed between double quotes and can have one of the following values:

The Number argument specifies the number of Interval units you want to add to the DateValue value. If you set it as positive, its value will be added. Here are examples:

Private Sub cmdDateTime_Click()
    Dim LoanStartDate As Date
    Dim DepositTime As Date

    LoanStartDate = #6/10/1998#
    DepositTime = TimeValue("7:14:00")
    
    MsgBox "Loan Length: " & DateAdd("yyyy", 5, LoanStartDate)
    MsgBox "Time Ready: " & DateAdd("h", 8, DepositTime)
End Sub

This would produce:

Instead of adding a value to a date or a time value, you may want to subtract. To perform this operation, pass the Number argument as a negative value. Here are examples:

Private Sub cmdDateTime_Click()
    Dim LoanPayDate As Date
    Dim TimeReady As Date

    LoanPayDate = #8/12/2008#
    TimeReady = TimeValue("17:05")
    
    MsgBox "Loan Length: " & DateAdd("m", -48, LoanPayDate)
    MsgBox "Time Deposited: " & DateAdd("n", -360, TimeReady)
End Sub

This would produce:

Another valuable operation performed consists of finding the difference between two date or time values. To help you perform this operation, the Visual Basic language provides a function named DateDiff. This function allows you to find the number of seconds, minutes, hours, days, weeks, months, or years from two valid date or time values. The DateDiff function takes 5 arguments, 3 are required and 2 are optional.

The syntax of the function is

Function DateDiff( _
    ByVal Interval As [ DateInterval | String ], _
    ByVal Date1 As Variant, _
    ByVal Date2 As Variant, _
    Optional ByVal DayOfWeek As Interger = 1, _
    Optional ByVal  WeekOfYear As Integer = 1 _
) As Long

This function takes five arguments, three of which are required and two are optional.

The Date1 argument can be the start date or start time. The Date2 argument can be the end date or end time. These two arguments can also be reversed, in which case the Date2 argument can be the start date or start time and the Date1 argument would be the end date or end time. These two values must be valid date or time values

The Interval argument specifies the type of value you want as a result. This argument will be enclosed between double quotes and can have one of the following values:

Here is an example:

Private Sub cmdDateTime_Click()
    Dim LoanStartDate As Date
    Dim LoanEndDate As Date
    Dim Months As Long

    LoanStartDate = #8/12/2003#
    LoanEndDate = #10/5/2008#
    Months = DateDiff("m", LoanStartDate, LoanEndDate)
    
    MsgBox "Loan Start Date: " & vbTab & LoanStartDate & vbCrLf & _
           "Loan End Date: " & vbTab & LoanEndDate & vbCrLf & _
           "Loan Length: " & vbTab & Months & " months"
End Sub

This would produce:

By default, the days of a week are counted starting on Sunday. If you want to start counting those days on another day, supply the Option1 argument using one of the following values: vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, vbSaturday. There are other variances to that argument.

If your calculation involves weeks or finding the number of weeks, by default, the weeks are counted starting January 1st. If you want to count your weeks starting at a different date, use the Option2 argument to specify where the function should start.

We saw that we could use the DateDiff() function to get the difference between two date or time values. The first argument can be specified as a string. A better idea is to use a member of the DateInterval enumeration. The members are:

By default, the first week of a year is the one that includes January 1st of that year. This is how it is considered in the regular date-based calculations. If you want to change this default setting, you can use the last argument of the DateDiff() function. The value of this argument can be:

In previous sections and lessons, we studied various sections available in the Visual Basic language. Besides the libraries used in Microsoft Access, the Microsoft Windows operating system provides its own library of functions and objects. This library is called the Win32 Application Programming Interface or Win32 API, or simply Win32. The Win32 library is somehow available to applications but its functions are not directly available for a database.

The Win32 library is made of procedures, functions, and classes (mostly structures) that you can use to complement a project. There are so many of these functions and objects that it is hard to know most or all of them. The best way to get acquainted with them is to check its documentation. To do this, you can visit the MSDN web site. The functions are stored in various sub-libraries called dynamic link libraries (DLLs).

Before using a Win32 function in your code, you must first have two pieces of information: the DLL in which the function was created and the actual name of the desired function in that library. Examples of DLLs are shfolder or Kernel32. Once you know the name of the library and the name of the function you want to use, you must import it in your Visual Basic code. The basic formula to follow is:

Private Declare Function Win32FunctionName Lib "LibraryName"
	Alias "CustomName" (Arguments) As DataType

The Win32FunctionName factor is the name of the function in the Win32 library. The LibraryName is the name of the library. You can create a custom name for the function as the CustomName factor. In the parentheses, you can enter the names and types of the arguments. If the procedure returns a value, you can specify its type after the As keyword.

Here is an example:

Option Compare Database
Option Explicit

Private Const MAX_PATH = 260
Private Const CSIDL_PERSONAL = &H5&
Private Const SHGFP_TYPE_CURRENT = 0

' We will use the Windows API to get the path to My Documents
Private Declare Function SHGetFolderPath Lib "shfolder" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, ByVal nFolder As Long, _
    ByVal hToken As Long, ByVal dwFlags As Long, _
    ByVal pszPath As String) As Long

Private Sub cmdCreateDatabase_Click()
    Dim strMyDocuments As String
    Dim strDbName As String
    Dim valReturned As Long
    Dim dbMVD As DAO.Database
    
    ' Initialize the string
    strMyDocuments = String(MAX_PATH, 0)
    
    ' Call the Shell API function to get the path to My Documents
    ' and store it in the strMyDocuments folder
    valReturned = SHGetFolderPath(0, CSIDL_PERSONAL, _
                                  0, SHGFP_TYPE_CURRENT, strMyDocuments)
    ' "Trim" the string
    strMyDocuments = Left(strMyDocuments, InStr(1, strMyDocuments, Chr(0)) - 1)
    ' Include the name of the database in the path
    strDbName = strMyDocuments & "\Motor Vehicle Division.mdb"
    
    ' Create the database
    Set dbMVD = CreateDatabase(strDbName, dbLangGeneral)
End Sub