Ms Access Datepart Function and Syntax Query

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

Read Also:  MS Access Validation Rule Lookup Table Examples

 

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

Related Access Database

  • ms access datepart function


Access Database Tags: #ms access concatenate datepart #ms access datepart 2 digit month #ms access datepart 2 digit year #ms access datepart day name #ms access datepart day of week #ms access datepart example #ms access datepart function #ms access datepart month and year #ms access datepart month name #ms access datepart of datetime #ms access datepart short date #ms access datepart weekday #ms access get date part from datetime #ms access get date part of datetime #ms access group by datepart

MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select


Must read×

Top