Weekday Function in MS Access

In this module, we will learn about the function and implementation of the Weekday function in MS Access.

The Weekday function assigns and returns a value between 1 to 7. Each of the returned value is mapped with a weekday.

Syntax

The syntax for implementing the Weekday function is as follows:

Parameter and Argument Entered in the Weekday Function

Date: The first parameter should be a valid date that could be represented on the calendar. The format of the date should be in the proper format. The date can either be numerical or can also be a string expression that points to the date. If the value is Null, then the function will also return Null.

Firstdayofweek: It is an optional argument that can be entered into the function. By default, the first day of the week is Sunday. But using this argument, we can change this default value. The argument can take the following values:

Value Returned by the Weekday Function:

Note: While implementing the Weekday function in the VBA, the firstdayofweek parameter has two possible ways of giving input as a parameter in the function, i.e., using numerical values or the command equivalent. But when you are implementing the function in the query, it will only accept the numeric value as the input parameter.

Can be Implemented in:

The Weekday function can be used in the given versions of the MS Access application: Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

In case of later versions, refer to the documentation of the updated versions.

Example

Let us implement the Weekday function in your MS Access application:

Thus, the day on 11/09/2022 was Sunday, and the function's execution will return 1 as the numeric value.

Thus, vbWednesday is the value for the optional parameter firstdayofweek. This will assign Wednesday as 1. Thus, it will return 5 for Sunday.

This will assign Tuesday as 1. Thus, it will return 6 for Sunday.

Implementing Weekday in VBA Code

Only a few functions can be implemented in the query and VBA. In MS Access, you can use the Weekday function in queries and VBA.

Let us see an example to implement the function in VBA:

The above code defines Monday as the first day of the week. It was Saturday, 10/09/2022. Thus, the variable LDayoftheWeek will contain the value 6.

In the above code, Tuesday is the first day of the week. It was Saturday, 10/09/2022. Thus, the variable LDayoftheWeek will contain the value 5.

Implementing Weekday in Queries

Let us implement the Weekday function as a query in MS Access:

The function will determine the day of the week on the entered Date. In this case, it is Monday, and since Monday is also assigned as the first day of the week by the second parameter. The final value returned will be 1. this value will be stored in the column named Expressn1. You can also change the column's name to make it more relatable.

In the above example, instead of using the Date directly, we have used a field named OrderDate the function will take the Date from the field. Suppose the Date in the field is 08/09/2022. It will assign Sunday as 1 since it was Thursday on 08/09/2022. The function will return 5. the value will be stored in DayValue.