Access: DateDiff function clearly explained
Special functions are available in Access for date calculation. With DateDiff you determine the difference between two date values. You can find out how to set up this function here.
Use the DateDiff function in Access
In Access, you can use the DateDiff function to calculate the number of days between two date values.
- The syntax looks like this: DateDiff (interval, date1, date2 [, firstdayofweek] [, firstweekofyear]).
- Optional entries are in square brackets.
- The parameters in parentheses are the ones that you need to include in the "Interval" syntax.
- The following selection options are available: seconds (s), minutes (s), hours (h), weeks (ww), days of the week (w), days (d), days of the year (y), months (m), Quarters (q) and years (yyyy).
- For example, the expression = DateDiff ("m", date1, date2, 2, 2) calculates the difference between two date values in months.
- The "m" stands for "month" in English and calculates the difference in months. The values "date1" and "date2" are the dates required to calculate the difference.
- You can optionally use the last two parameters (2, 2).
- With the first parameter (2 = firstdayofweek) and the second parameter (2 = firstweekofyear) you define the first day of the week and the first week of the year.
- If you do not enter anything here, the first day is Sunday and the first week, the week that includes January 1st.
- Use entry 2 to specify that the first day of a week is a Monday (2 = vbMonday, 3 stands for Tuesday, 4 for Wednesday, etc.).
- In the next step, you use parameter 2 to specify that the first week with at least 4 days in the new year is the first calendar week.
- If you want the calculation to start on the first full week of the year, enter a 3.
- You can use the DateDiff function in an expression as well as in the VBA code.