Date and Time-Based Functions

Now - Date - Time

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(), Time(), and Now() functions to display their values.

The Date() function is used 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.

The Time() function is used to get the system time of the computer. To display the system time in a text box, you can enter =Time() in its Control Source property.

The Now() function combines the system date and time of the computer.

1. On the main menu of Microsoft Access, click Tools -> Macro Visual Basic Editor

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

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

4. Notice that the system date is displayed

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

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

The Day() function is used to get the numeric value that represents a day in the month. It ranges from 1 to 31 included. The syntax of the Day() function is

Day(DateValue)

The Month() function displays the numeric month of a date. It ranges from 1 to 12 included. The formula of the Month function is

Month(DateValue)

The Year() function returns the numeric year of a date. The syntax of th is function is

Year(DateValue)

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
11. Close Microsoft Visual Basic and close Microsoft Access

The DateAdd() function can be used to add an interval value, such as a number of days, weeks, months, or years to another date. Its syntax is:

DateAdd(Interval, Number, date)

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

Required also, the Number argument specifies the number of units you want to add. If you set it as positive, its value will be added. If you want to subtract, pass it as a negative value. The number represents how many units of the Interval argument you want to add.

The date argument is the date to which you want to add the number.

The DateDiff() function is used to find the difference between two date or time values. It allows you to find the number of seconds, minutes, hours, days, weeks, months, or years when you supply two recognizable values. The DateDiff() function takes 5 arguments, 3 are required and 2 are optional. The syntax of the DateDiff() function is

DateDiff(Interval, Date1, Date2, Option1, Option2)

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

Required also, the Date1 and Date2 arguments specify the date or time values that will be used when performing the operation.

By default, the days of a week are counted starting on Sunday. If you want to start counting those days on another day, pass the Option1 argument using one of the following constants: 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 program should start.