In this tutorial we will discuss about Ms Access Datepart function and syntax. The DatePart function can be used for to evaluate a date and return a specific interval of time, so you can use it for other function for calculate the day of the week or the current hour.
Syntax
for DatePart function is:
DatePart ( interval, date, [firstdayofweek], [firstweekofyear] )
Parameters
Interval: the time type that will be returned. The type of the interval can be seen in this table:
| Interval | Explanation |
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
Date: the date value that going to be evaluate.
[firstdayofweek]: the parameter to determine the first day of the week. It is optional field, so if it omitted the default value will be Sunday.
| Constant | Value | Description |
| vbUseSystem | Use the NLS API settings | |
| vbSunday | 1 | Sunday (default used) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
Note: If the Weekday function is used in query, for the first day of week parameter you need to input the numeric value (e.g. 0 – 7). While in VBA code, input the constant value (e.g. vbTuesday).
[firstweekofyear]: it is almost like [firstdayofweek], only it determine the first week in the year. If omitted, the default will be the week that contains 1st January.
| Constant | Value | Explanation |
| vbUseSystem | Use the NSL API setting | |
| vbFirstJan1 | 1 | Use the first week that includes Jan 1st (default)
E.g. 1st Jan 2016, despite it is Friday. |
| vbFirstFourDays | 2 | Use the first week in the year that has at least 4 days
E.g. 1st Jan 2008 is the first week because it has 5 days. |
| vbFirstFullWeek | 3 | Use the first full week of the year
E.g. the first week in 2016 is 3rd. |
Example:
1. DatePart (“q”, #24/03/2017#)
Result: 1
2. DatePart (“w”, #24/03/2017#, vbMonday)
Result: 5
3. DatePart (“ww”, #24/03/2017#, , 3)
Result: 12
Example in VBA
code
There is not much difference in the VBA code, except you need to add the syntax in the first line as the argument for the return code.
Example in Query
In the query sample above, we create a Test field to count the week number started with the first full week of the year.