Wed. May 22nd, 2024
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function

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).

Read Also:  How to Fix MS Access Subscript Out of Range Error

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:

MS Access Weekday Function
MS Access Weekday Function

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:

Ms Access Weekday-2

 

MS Access Weekday Number
MS Access Weekday Number