Thu. Feb 22nd, 2024
Ms Access Datepart Function and Syntax Query
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).

 

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

Read Also:  Access Templates Incident Management System and Report Database
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.