Keep the date current in your calculations—leverage built-in date functions

 

by Julie Duncan

Application:

Microsoft Access 2016

Operating System:

Microsoft Windows

Using Microsoft Access for your business calculations? If doing so requires today’s date, you may find it a challenge to keep the date current given that it’s constantly changing? Thankfully, Access provides handy, customizable date functions that allow you to use the date in your calculations without having to worry about your databasebecoming dated.

To keep your database up-to-date, we’ll:

  • Take a closer look at twostraightforward date functions.
  • Utilize these date functions in different parts of your database.
  • Format the database properties so you get precisely the results you need.

 

Dates provide valuable information for your database in so many ways, whether you simply want to be sure today’s date appears at the top of a report, or you’re pulling data from every invoice submitted in the past 30 days. Keeping your dates current is crucial to the accuracy of your printouts and calculations. We’ll let Access do the hard work of ensuring your data is up-to-date by making use of built-in date functions.

First, we’ll take an in-depth look at our options for date functions. Then we’ll use these date functions within the database to get a feel for how they work. Finally, we’ll help you format the results to display the right information for any situation.

Learn about date functions

We’ll explore two functions that return the date—or part of it. These functions work in all parts of your databases: queries, tables, forms, and more.

The Now() function

When you want to display the current date and time, use the Now()function. This function will return information that reads like this: 12/17/2016 11:23:18 A.M.

Tip: Keep in mind that Access uses the date and time displayed on your computer’s system clock, relying on the accuracy of this clock for correct results in your calculations.

The Date() function

The Date() function works similarly to Now(), except that it returns the date only. This function will return something like:12/17/2016.

Apply the Now() and Date() functions

Now that we understand how these functions work generally, let’s practice incorporating them in various database objects. First, we’ll display the current date and time on a form, and then we’ll enter a date as the default value for a new record in a table.In our example, Access will automatically enter the current date in a field each time you add a new entry to your form.

To display the date and time on a form:

  1. Open a form in an Access database, or create a new one. Switch to Design View.
  2. On the contextual Form Design Tools Design tab, in the Header / Footer area, click on Date And Time.
  3. Select your preferred settings in the Date And Time dialog box, or follow our settings in Figure A. Click OK.
  4. Click and drag to move the controls if needed.
  5. Save the form, and then click View to see how the date and time appear on the form.

Need help? Switch to Design View by selecting the Home tab and clicking on the View dropdown arrow in the Views group. Choose Design View from the resulting dropdown menu. Use the View button or the View dropdown arrow to toggle through other views as needed.

A:

IME18103A

In the Date And Time dialog box, you determine how much information will appear, and you format that information.

To create a default value in a table:

  1. Open a table in any Access database, or create one in Design View that resembles[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage