Using dates and times in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

Whether you create your own databases, or use databases that someone creates for you, you have often come across situations where you had to work with date and time values. Similar to text and numeric values, date and time values can be stored in tables, and displayed in forms and reports. You can filter, sort, and group on date and time values. However, they are bit more complex than text and numeric values, because of how they are stored and the various display formats they commonly use. Also, you might often not be interested in the entire date or time value, but in only a portion of it, such as the month.

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so it’s easier to use that date function – or any function.
Watch a video or try Office 2010.

This topic describes all the different ways you can work with data and time values — how to enter, display, use, and calculate them.


Table of contents

What can you do with date and time values
Sample data for use with this article
Entering and storing data
Displaying data
Filter, sort, and group data on date and time values
Perform calculations that use date and time values
Additional resources

What can you do with date and time values

  • Store    You can enter date and time values in a variety of formats in Datasheet view, Form view, and Page view. The values will be stored in the corresponding field of type Date/Time in the underlying table. For example, you can store the hire dates of employees in the HireDate field of the Employee table.

Employees table with two date/time fields

Quarterly New Hires report, grouped by month, and sorted on hire date

  • Calculate    You can calculate the difference between two dates or two time values, or add or subtract a time interval from a date or time value. You can also extract just the month or hour portion of a value to manipulate it further. For example, you can calculate the number of years an employee has completed based on their hire date and the current date, or calculate when the employee will complete five years with the company.

Top of Page Top of Page

Sample data for use with this article

The examples in this article use the Northwind Traders sample database. If you don't know where to find this database, follow the instructions for your version of Access.

ShowStart Northwind.mdb using Access 2003 or Access 2002

  • On the Help menu, point to Sample Databases, and then click Northwind Sample Database.

ShowStart Northwind.mdb using Access 2000

  • Start Access, and in the opening dialog box, double-click Northwind Sample Database. If prompted, click Yes to install the sample database.

If you already have Access open, you can click the Open Database command on the File menu in any window, and then navigate to the Northwind Traders sample database. Northwind.mdb is usually in the Program Files\Microsoft Office\Office\Samples folder. If you already have another database open, Access will close it when it opens Northwind.mdb.

Top of Page Top of Page

Entering and storing data

Storing dates and times    Access provides the Date/Time data type to store date and time values in a table. You can store any value that follows a valid date and time format in such a field. For instance, you can use a European format ,such as dd/mm/yyyy, or the U.S. format (mm/dd/yyyy). You can also enter dates as a combination of letters and numbers, such as 29 Mar 08, or March 29 2008. You can also use any valid separator, such as a slash (3/29/2008), spaces (03 29 2008) and dashes (3-29-08). However, you must use a valid date format. You can't, for example, enter 32908, 03292008, and so on. If you do, Access will display an error message.

Formatting dates and times    Since a date/time field accepts input in a variety of formats, you might be wondering how to stick to a single format for the sake of being consistent in your forms and reports. How do you ensure that dates are stored and displayed in a standard format? You can do this by setting the InputMask and Format properties of the date/time field. The InputMask property ensures that the values are entered and stored in a valid, specific format. The Format property ensures that the values are displayed in a specific format. For example, you can set the InputMask property of the HireDate field to a format that is easier to type, such as 99/99/9999. This will ensure that users enter all values in mm/dd/yyyy format. However, you can set the Format property to one that is easier to scan or read, such as "01-May-1992". Note that the Format property only specifies the default format, and you can specify a different format for a control on a form or a report that displays the values in this field.

For more information on setting input masks and display formats, see the following topics:

Input mask syntax and examples

Create an input mask

About display formats for fields or controls

Set the display format for data in a field or control

Specifying default values    When entering data, you may frequently come across a situation where most records use the same value for a date/time field. For example, if you are entering data on orders, the value for the OrderDate field might be the current date. To simplify data entry, you can set the DefaultValue property of the Date/Time field to the current date, to save some typing.

For more information about how to set the DefaultValue property of a date/time field, or for a control that is bound to a date/time field, see Set a default value for a field or control.

Using a calendar or clock control to enter date and time values    Another way to reduce typing is to use a calendar or clock control to enter date and time values. The following illustration shows a calendar control on a form that you can use to enter the hire date of an employee.

Form that provides a calendar for entering date values

As you can see, a calendar control looks different from typical Access controls, such as a text box or a combo box. A calendar control is an ActiveX control (ActiveX control: A control, such as a check box or button that offers options to users or runs macros or scripts that automate a task. You can write macros for the control in Microsoft Visual Basic for Applications or scripts in Microsoft Script Editor.), and has a custom behavior that you can manipulate by setting its properties. In the form illustrated above, you can specify a date by selecting the month and year in the combo boxes at the top of the control, and then clicking the box corresponding to the desired date.

Access provides an ActiveX control named Calendar Control 11.0, that you can place on your forms, reports, and data access pages.

For more information about how to add and work with this control, see Using the calendar control in Access.

Similarly, you can use a clock to enter time values. However, Access does not provide a clock control. But you can create your own custom clock functionality by creating a few text boxes and adding code.

For more information about how to add a clock to your form, report, or page, see Add clock functionality to a form in Access.

Adding and editing values    You can enter date/time values in Datasheet view, Form view, or Page view. To do this, you move to a column or a control, and type a value in the same way you do for text or numeric values. If an input mask is set for a control or its underlying field, you will be forced to enter a value that matches the format specified by the mask. If a validation rule is set for the date/time field or for the record, you must enter a value that satisfies the rule.

For more information about adding and editing data, see Add, edit, and delete data. Also see Modify a date in Access by clicking the mouse.

Top of Page Top of Page

Displaying data

Display date and time values stored in the database    You can display date and time values in datasheets, forms, reports, and data access pages. When designing a query, form, report, or data access page, simply add the date/time field. By default, the corresponding column in a Datasheet view and the control on a form, report, or page will inherit certain property settings from the date/time field, such as default value, input mask, or display format. However, if you want a different format or to specify a default value for a form or a report, you can set the corresponding property of the control.

For more information about how to add a date/time field to a form, report, or data access page, and set its properties, see the following topics:

Add a field to a form, report, or data access page

Input mask syntax and examples

Create an input mask

About display formats for fields or controls

Set the display format for data in a field or control

Set a default value for a field or control

Display current date and time on a form or a report    You might want to insert a control to display the current date or time — for example, on a report, to inform its users when it was generated. The date and time values will not be stored in the database, but can be displayed and printed on both forms and reports.

For more information about how to include current date and time, see Show current date and time.

Displaying date and time stamps on a form, report, or page    Sometimes, you might want to display the time a record was created or last modified on a form or a report. You can do this by writing a macro or a few lines of Visual Basic® for Applications (VBA) code to capture when an event (such as saving or modifying a record) takes place, and store that time value in an unbound control on the form, report, or data access page.

To record the last modified date and time for records in a form, see Recording the date and time when a record is modified in Access. To record other events, identify the corresponding form property or event, and write a macro or procedure that is similar to the one explained in the linked topic.

Top of Page Top of Page

Filter, sort, and group data on date and time values

Filter a query, form, or data access page    You can limit the records that are displayed in a form or query, based on date and time values. For example, you can filter the query named Orders Qry to show only the orders placed during the year 1996. Or, you can see all the orders shipped on 28-Aug-1996. You can filter records based on date and time values in the following ways:

  • Display records containing a specific date or time value, such as orders shipped on 28-Aug-1996. You can also filter based on a portion of a date or time value, such as orders shipped in the month of August 1996.
  • Display records not containing a specific value, or the portion that is selected. For example, view records of employees not hired during the current year.
  • Display records containing Null values. For example, display all orders that don't have a value in the ShippedDate field.
  • Display records based on one or more criteria. For example, show orders that were shipped more than 60 after their order date. Or, display records of employees who are age 55 and older.
  • Display records that are in the top or bottom n values or percentage. For example, you can filter for the five most-recently hired employees, or for the top 10% of the products that are selling well.

For more information about how to filter records based on date and time values, see the topic Filter: Limit the number of records in a view or report.

Specify criteria based on dates and times when designing a query    When creating a query, you can specify one or more criteria to retrieve only a specific set of records from one or more tables. For example, the Product Sales for 1997 query includes the criterion Between #1/1/1997# And #12/31/1997#, to include only the sales data for year 1997.

If you want some flexibility with the criteria, create a parameter query. A parameter query lets you specify the criteria just before the query is run. For example, instead of creating separate sales queries for each year, you can create a query that lets you specify the year for which you want the data. For example, a single parameter query can get you the records for 1997, 1998, or 1999, depending on the value you specify when prompted.

For more information about how to specify query criteria and create parameter queries, see the following topics:

Enter criteria to retrieve certain records

Where to enter criteria in queries

Using parameters with queries

Sort records based on dates and times    You can sort records in a datasheet, form, report, or data access page on date and time values. When you sort records in ascending order, the record with the oldest date or time value appears at the top, and the record with the most recent date or time will appear at the bottom. For example, when looking at a list of new employees hired during the month, you might want to see the data, sorted on hire date.

For more information about how to sort data, see Sort records.

Group records based on dates and times    You can group records in a report or on a data access page, based on date and time values. In Design view, set the GroupOn and GroupInterval properties to specify how you want the records grouped. For example, to view the total annual sales figures, set the GroupOn property to Year and GroupInterval to 1. To view bi-weekly sales figures, set the GroupOn property to Week and GroupInterval to 2.

Top of Page Top of Page

Perform calculations that use date and time values

Writing an expression to calculate a value    As with numeric values, you can perform a variety of calculation operations using date and time values. You calculate a value in Access by writing an expression. An expression is similar to a formula — it is a combination of operators, constants, literal values, functions, and names of fields, controls, and properties that evaluates to a single value.

Date and time functions    Most calculations using date and time values involve using the built-in date/time functions. For example, to add a week to a specific date value, write an expression that uses the DateAdd function. The following expression adds a month to the date value 31-Jan-04. The expression returns the value 29-Feb-04.

=DateAdd("m", 1, "31-Jan-04")

Here's another example of an expression that uses the DateDiff function to find the number of days between two date values. The expression returns the number of days between the date the order was placed and the date the order was shipped.

=DateDiff("d", [OrderDate], [ShippedDate])

Other commonly-used functions include Date, DatePart, Now, and Weekday. To see the complete list of date/time functions, open the Expression Builder (on the Form Design toolbar, click Build to open the Expression Builder). In the lower left list box, expand Functions, Built-in Functions, and then click Date/Time. The right-most list box displays all the built-in date/time functions. To get help on a specific function, select the function name, and click the Help button that is above the list box.

For more information on how to write expressions and see examples of date/time expressions, see the following topics:

Create an expression

On time and how much has elapsed

Add to or subtract from date and time values

Calculating a future or past date in Access

Calculating age from a birth date in Access

Calculate the difference between two date or time values

Calculate the serial number of the day, week, or month in a date

Combine text, number, date, and time values

Extract a part of a date or time value

Format date and time values

Use current date and time in calculations

Top of Page Top of Page

Additional resources

For information about using date and time values in Access, read the following Microsoft Knowledge Base articles:

Storing, calculating, and comparing date/time data

Functions for calculating and displaying date/time values

Date/time data type provides current year automatically

Top of Page Top of Page

 
 
Applies to:
Access 2003