So what does DatePart actually do? Put simply, the DatePart Function allows us to isolate part of a given date. In so doing we may then go on to group or retrieve records according to the date interval set . We use the function by passing an interval parameter telling Access which part of the date we are interested in, and a second parameter which is the date itself. The syntax for the function is constructed as follows:
The result of the function gave us a numeric month value based on the full date. For example, if the date in fldDate had been 16/09/2011, the function would have returned the value 9. This is useful when we have a list of dates, such as that in Figure 1 below, which we want to group by month value in a query or report.
|Figure 1: List of Orders with dates.|
So lets take a look at how we would use the DatePart Function in a query to group these order records by month:
As you can see in Figure 2 above, we have created a calculated field called TheMonth using the DatePart Function. The interval, "m", has been passed in the first parameter, and the second parameter references the OrderDate field from tblOrders. We have also clicked the TOTALS icon from the SHOW/HIDE group of the DESIGN ribbon, setting TheMonth and Total fields to GROUP BY and SUM respectively.
When we run the Query, any month containing an order will be represented in the results as its own individual row. The first column then displays the Month value derived from the DatePart function in TheMonth, and the second contains the sum of order totals for the particular month in question. Figure 3 below shows the query result for our sample data:
|Figure 3: Results of Query with Calculated|
Field using the DatePart Function. SumOfTotal
gives us the total for each group of order records
falling within each Month.
Another excellent use of DatePart would be to filter a group of records using a criteria based on this function. For example, if we wanted to show each individual record for all orders falling within the month of May, we would construct our Query as follows:
|Figure 5: The result of the second Query filters |
out all orders from May.
If we wanted, we could convert this to a parameter query, and use it as the data source for a report. This was something I recommended to NorAzri who wanted to select the Month from a Combo Box on an unbound form, and then produce a report which would filter out records falling within the month in question. If you are interested in seeing the solution I suggested, just follow the link at the top of this post and look down the stream.