Ms Access Weekday Function is purposed to return a number presenting the day of the week to a date. The result will be returned as number (integer data type) from 0 to 7. In the Access Query, you can add the field name into the Date parameter, as long the field value is Date. Following the picture above, the field name is BorrowDate. If you input the field name, don’t forget to add square bracket “[]” to the name.
The syntax for Weekday function in Ms Access is this:
Weekday ( date, [first day of week] )
Date is the required parameter. The format should be DD/MM/YYYY or other format that represent the Date.
First Day of week is the optional parameter. It will change the first day of the week. If it omitted, the first day of the week will be Sunday as the default value.
The first day of the week has this setting and value:
| 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).
Example:
1. Weekday (#12/03/2017#)
Result: 1
2. Weekday (#12/03/2017#, 2)
Result: 7
3. Weekday (#12/03/2017#, vbTuesday)
Result: 6
Explanation:
The Example #1 is using the default value. Since 12 March 2017 is Sunday, so the value will be 1.
In Example #2, the first day of the week changed to 2 or Monday, so the day value is shifted. As the result, the returned value is 7 instead of 1.
The Example #3 is almost same like Example #2, only differ in syntax.
Example in VBA code:
Dim LWeekday As Integer
LWeekday = Weekday (#12/03/2017#, vbTuesday)
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 Access Query:
