MS Access 2010 DATE Functions

Access encompass variety of powerful tools, that facilitates user to use dates and date ranges to be specified in a certain criteria. To get by with the complex logic in your database that contains dates, then DATE functions could be very useful for you. This post will elaborate how to use simple DATE functions.

Launch Access 2010, open a table in which you want to apply Date function. For instance, we have included a table, containing record of account holders in several fields; Name, Account, Cost, Date, and Cancellation as shown in the screenshot below.

Now we need to create a query in which we will apply Date function to check the Due Date, Difference of days etc. for this head over to Create tab, and click Query Design.

You will reach a query tab, a window will pop-up asking you to add desired table, choose the desired table, and click Add.

Selected Table window will appear, showing it’s fields, drag each field to the bottom pane and enable check boxes, as shown in the screenshot below.

Now we will add a new field in this query, suppose we want to create a fields which shows the due date. For adding a Due Date field we assume that it would be 7 days after you sign-up for an account.

So we will write it as

Due: [Date]+7

Note: When you are about to work on dates, make sure that it’s data type must be set as Date/Time, for performing actions and applying functions on dates. It happens sometime that field contain dates, has a data type of Text or Memo, which also supports inclusion of dates, as writing dates would not invoke any conflict with Text or Memo data type.

We will write the formula in the last cell adjacent to Cancellation cell as shown in the screenshot below.

Click Run, and you will notice that it will add up 7 days to the date of commencement and show us the result in newly created field Due, as shown in the screenshot below.

Now if want to show the difference between the date of commencement and cancellation date we will simply write;

Difference: [Cancellation]-[Date]

Upon clicking Run, it will show the difference between the dates as numeric values, as shown in the screenshot below.

Now if we want to extract the month and year from any field, containing dates, we will simple write following lines;

Month: Month([Table name])

Year: Year([Table name])

It will extract year and month from the Date field, as shown in the screenshot below.

We have applied some basic Date functions over the table, there are lot of other ways in which you can use them according to the situation, it was the simple example to get you started with Date function, but when you need to compare multiple fields containing dates, then it would become complex.

You can also check out previously reviewed guides on Access 2010 Relationship & Query interconnects and how to analyze Access reports with Excel.

Read Full Bio

Usman Javaid

Follow me on Google+

Latest from Author

How To Create A Portable Windows 8 Enterprise To Go Workspace How To Install Windows 8 As Guest OS Using VMware Player