MS Access 2010 DATE Functions

Advertisement

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.

table account

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.

query design 1

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.

table query 1

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.

due date 1

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.

run 1

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

Difference: [Cancellation]-[Date]

difference 1

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

difference run

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

year and month 1]

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

year and month tablem 1

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.

Advertisement
  • Kingjosesmith

    Doesnt say anything about the Date() function.  It will not work in Access like the Now() function does.

  • crmaginn

    Date() function?

  • Brilliant! This helped me a lot!. Figured out the “Year For Retirement for my ongoing project through Access 2010. Appreciate! Just combination of what you gave plus “=(Now()-[DOB])365” and combination of different Mathematical opperations i got e’thing i wanted!Thanks a lot! 🙂

  • Brilliant! This helped me a lot!. Figured out the “Year For Retirement for my ongoing project through Access 2010. Appreciate! Just combination of what you gave plus “=(Now()-[DOB])365” and combination of different Mathematical opperations i got e’thing i wanted!Thanks a lot! 🙂

  • Doug Lowe

    Thanks for making this easy to apply right away 🙂 Much appreciated!

  • Anne

    What would the expression be to add 5 years to the date due and have the response show date as day/month/year?