databasedev.co.uk - database solutions and downloads for microsoft access

Dashboard Builder for Microsoft Access
Need to see key data from your Access database in a single dashboard? Tired of creating query after query to really understand your Access data? Dashboard Builder for Microsoft Access is an easy to use tool to create powerful dashboards in minutes…without being a SQL wizard. Free trial available

The DatePart() Syntax

The DatePart Function

In Microsoft Access, we can use the DatePart function to evaluate a date and return a specific interval of time. For example, you might use DatePart to calculate the day of the week, the year value from the date, or the current hour.

Syntax

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has the following named arguments:

Part Description
interval Required. String expression that is the interval of time you want to return.
date Required. Variant (Date) value that you want to evaluate.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Ignoring the optional parts of the DatePart syntax, we use the interval as the time that we want to return from the function and the date is the date value that we want to evaluate.

Using the DatePart Function in a Microsoft Access Query

In the following example, we are going to use the DatePart function within a query.

In the scenario, the data that we will use is a list of sales of Movies. Each sale contains an Order Purchase Date. We will use the DatePart function to extract the Year from our date, which we can then use to group our records on in the query. Below we can see how the data appears in the Orders table:

The Movie Orders table

As you can see we have a Purchase Order Date, that records when the sale of the item was made. If we want to analyse the data, we may want to see how many sales, and how much value was made in sales per year.

If we create our query, we will include the following fields in the design:

Creating the query.

Using these fields will give the following initial results, that at the moment don't provide much information. They don't give us a count of how many sales or how much profit has been made:

The resultant data from the initial query design.

What we can now do, is create an expression in the query design, that will provide us with the Year that the Order was made. We use the following expression to provide us with the value:

OrderDateYear: DatePart("yyyy",[dtePurchaseOrderDate])

Our query design now appears as follows, with the expression replacing the dtePurchaseOrderDate field in the query design:

Including the DatePart function in an expression in the query design.

Again, when the query is ran, the results are still not that meaningful. The only difference now is that we can view the year that the Order was made, as a result of using the DatePart function in the expression:

Viewing the Order Year after applying the DatePart function in the query design.

To allow us to view meaningful data to analyse, we should add some Grouping and Aggregate functions in our query design. The following shows the addition of the Totals Row in the query design. Using this, we then group the data on the expression that uses the DatePart function, and add a Count to the MovieID field, and a Sum to the OrderCost field:

Showing the grouping and aggregate functions in the query design.

The following results can then be viewed by running the query:

The results, now grouped on the Year value created from the DatePart expression.

The results are now grouped by the Order Date Year that we have gained by using the DatePart function on our Order Date field.