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:  Ms Access Outer Join SQL Function

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

 

 

Related Access Database

  • access week
  • ms access day of the week
  • search function in access query


Access Database Tags: #ms access get weekday from date #ms access weekday #ms access weekday function #ms access weekday function example #ms access weekday number #ms access weekdayname

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access


Must read×

Top