Insert, create, or delete a field that stores date values

You add a Date/Time field to a table when you need to store dates and times. You can use date and time data for a variety of personal and business reasons, such as birthdays, shipping and billing information, and time cards. This article explains how to use Microsoft Office Access 2007 to add and delete a Date/Time field in new and existing database tables.

Note: This article does not explain how to enter data into a Date/Time field. For more information about ways to use Date/Time fields, see the article Enter a date or time value.

In this article

Understand Date/Time fields

Add a Date/Time field in Datasheet view

Add a Date/Time field in Design view

Delete a Date/Time field

Date/Time field property reference

Understand Date/Time fields

If you are new to Office Access 2007, remember that the data in a database is stored in one or more tables. You can view the information in a datasheet — a grid that resembles a Microsoft Office Excel 2007 worksheet — or in a data-entry form or a report, but the data ultimately resides in one or more tables. In turn, each table consists of a set of fields (columns), and each field is set to accept one specific type of data. For example, you set a field to the Date/Time data type when you need to store dates and times, and those are the only types of values that the field accepts.

Date/Time field specifications

When you use an Access database, you might see date and time values in any number of formats, such as a European format (28.11.2006 or 28-11-2006), a South Asian format (28/11/2006), or the U.S. format (11/28/2006).

Regardless of how Access formats the date and time data, and regardless of how you enter the date or time data, Date/Time fields store the dates and times as double-precision floating point numbers — a system also called serial dates. The following figure represents a typical serial date and time value.

The integer portion of the value, to the left of the decimal point, represents the date. The fractional portion, to the right of the decimal point, represents the time.

The number in this figure represents December 24, 2003, at 9:00 P.M. The date component is the number of whole days that have elapsed since the starting or "base" date of 12/30/1899. In this example, 37,979 days have elapsed from 12/30/1899 to 12/24/2003. The time component is a fraction of a 24-hour day. So, a value of 0.875 multiplied by 24 hours equals 21 hours, or 9:00 P.M.

Negative values in the date component represent dates prior to the base date. For example, a value of -1 as the date component resolves to one day prior to the base date, or 12/29/1899.

Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). Valid time values range from 0.0 to 0.9999, or 23:59:59.

Storing the date and time values as numbers allows you to perform a large number of calculations on date and time data. For example, you can calculate a total number of hours worked (for a time card), or determine the age of an invoice.

Formats for Date/Time fields

You can use two broad types of formatting with a Date/Time field: data-entry formatting and display formatting. You use an input mask — a set of literal and placeholder characters that control how users enter data — when you need to provide data-entry formatting. You use display formats to control what users see in datasheets, forms, and reports. Access provides two types of display formats: a set of predefined formats that you choose from a list, and the ability to create a custom format.

For more information about using predefined and custom formats, see the article Format data in tables, forms, and reports.

For information about input masks, see the article Create an input mask to enter field or control values in a specific format.

Methods for creating a Date/Time field

Office Access 2007 provides several ways to add a Date/Time field to a new or existing table:

• Datasheet view    You can add a Date/Time field to a new or existing table in Datasheet view by adding a new field and then entering a date manually, or by copying and pasting a date. You can also select the data type from a drop-down list and set properties such as Is Required, which forces users to enter a date in the field, and Is Unique, which forces users to enter a non-duplicate value in the field.

• Design view    You use Design view to add a Date/Time field and set properties for the field that you cannot set in Datasheet view, including input masks and default values.

Top of Page

Add a Date/Time field in Datasheet view

The steps in this section explain how to add a Date/Time field to an existing table and to a new table in Datasheet view. If you are new to Access, a datasheet is a grid similar in appearance to an Office Excel 2007 worksheet.

Add a Date/Time field to an existing table

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. In the Navigation Pane, double-click the table that you want to change.

   Access opens the table in Datasheet view.

4. If necessary, scroll horizontally to the first blank field. By default, Access displays Add New Field in the header row of all new fields, like so:

   

5. Double-click the header row and type a name for the new field.

6. Select the first blank row under the header and type a date.

   -or-

   Paste a date into the first row.

   -or-

   On the Datasheet tab, in the Data Types & Formatting group, from the Data Type list, select Date/Time.

7. Save your changes.

Add a Date/Time field to a new table

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. On the Create tab, in the Tables group, click Table.

   Access opens the new table in Datasheet view. This figure illustrates a new table:

   

4. Click Save , and in the Save As dialog box, enter a name for the new table.

5. Double-click the header row for the first table field (the one labeled Add New Field), and type a name for the field.

6. Select the first blank row under the header, and type a date. Access infers the Date/Time data type automatically when you enter a date.

   -or-

   Paste a date into the first row.

   -or-

   On the Datasheet tab, in the Data Type & Formatting group, from the Data Type list, select Date/Time.

Top of Page

Add a Date/Time field in Design view

You use Design view to add a Date/Time field to a new or existing table and then to set or change the field properties that you can't set or change in Datasheet view. For example, you can specify an input mask or a validation rule. For more information about input masks and other properties, see the section Date/Time field property reference.

Add a Date/Time field to an existing table

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. In the Navigation Pane, right-click the table that you want to change, and then click Design View .

4. In the Field Name column, select the first blank row, and then type a name for the field.

5. Select the adjacent cell in the Data Type column, and then select Date/Time from the list.

6. Save your changes.

Add a Date/Time field to a new table

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. On the Create tab, in the Tables group, click Table.

4. Click Save , and in the Save As dialog box, enter a name for the new table.

5. Right-click the document tab for the new table and click Design View.

6. In the Field Name column, select the first blank row, and then type a name for the field.

7. Select the adjacent cell in the Data Type column, and then select Date/Time from the list.

8. Save your changes. If you want to set properties for the field, leave the table open in Design view and continue to the next steps.

Set or change the field properties

1. On the General tab in the lower section of the table designer, under Field Properties, locate the property that you want to change.

2. Select the field next to the property name. Depending on the property, you can do any of the following:

   • Enter data (such as default text or an input mask).

   • Start either the Expression Builder or the Input Mask Wizard.

   • Display the Smart Tags dialog box by clicking next to the property box.

   • Select an option from a list.

     For information about how to use each field property, select the property and then press F1.

Top of Page

Delete a Date/Time field

You can use Datasheet view or Design view to delete a Date/Time field from a table. However, when you delete a Date/Time field that contains data, you lose that data permanently — you cannot undo the deletion. For that reason, you should back up your database before you delete any table fields or other database components.

Delete a Date/Time field in Datasheet view

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. In the Navigation Pane, double-click the table that you want to change.

   Access opens the table in Datasheet view.

4. Locate the Date/Time field, right-click the header row (the name), and click Delete Column.

5. Click Yes to confirm the deletion.

Delete a Date/Time field in Design view

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. In the Navigation Pane, right-click the table that you want to change and then click Design View .

   Access opens the table in Design view.

4. Click the row selector (the blank square) next to the Date/Time field, and then press DELETE.

   -or-

   Right-click the row selector and click Delete Rows.

5. Click Yes to confirm the deletion.

Top of Page

Date/Time field property reference

When you use Design view to add a Date/Time field to a table, you can set and change a number of properties for the field. This table lists the Date/Time field properties and describes what each one does, including the implications of setting or changing the properties.

Property	Usage
Format	You enter custom formatting characters to define a display format. Formats defined here appear in datasheets, forms, and reports. For more information about custom formats, see the article Format data in tables, forms, and reports.
Input Mask	You enter an input mask string, or click to start the Input Mask Wizard. For more information about creating and using input masks, see the articles Create an input mask to enter field or control values in a specific format and Format data in tables, forms, and reports.
Caption	Specifies the name of your Date/Time field. If you don't specify a caption, Access applies the default field name.
Default Value	Specifies the value that automatically appears in a field when you create a new record. For example, you can enter a function such as Date() to automatically display today's date.
Validation Rule	Specifies requirements for data entered into an entire record, an individual field, or a control on a form or report. When a user enters data that violates the rule, you can use the Validation Text property to specify the resulting error message. Maximum length: 2,048 characters. For more information about creating validation rules, see the article Create a validation rule to validate data in a field.
Validation Text	Specifies the text in the error message that appears when users violate a validation rule. Maximum length: 255 characters. For more information about creating validation rules, see the article Create a validation rule to validate data in a field.
Required	When this property is set to Yes, you must enter a value in the field or in any controls that are bound to the field. In addition, the value cannot be null.
Indexed	You use an index to speed up queries, sorting, and grouping operations that are run against large amounts of data. You can also use indexes to prevent users from entering duplicate values. Choices: No     Turns off indexing (default). Yes (Duplicates OK)     Indexes the field and allows duplicate values. For example, you may have duplicate first and last names. Yes (No Duplicates)    Indexes the field and does not allow duplicate values.
IME Mode	Specifies an Input Method Editor, a tool for using English versions of Access with files created in Japanese or Korean versions of Access. Default value: No Control. For more information about using this property, press F1.
IME Sentence Mode	Specifies the type of data you can enter by using an Input Method Editor. For more information about using this property, press F1.
Smart Tags	You specify one or more smart tags for the field and any controls that are bound to the field. Smart tags are components that recognize the types of data in a field and allow you to take action based on that type. For example, you can select a Date/Time field and then use a smart tag to open your personal calendar. Click next to the property box to see a list of available smart tags.
Text Align	Specifies the alignment for data in a Date/Time field. Choices: General     Aligns text to the left, numbers and dates to the right. Left     Aligns all text, dates, and numbers to the left. Right     Aligns all text, dates, and numbers to the right. Center     Centers all text, dates, and numbers. Distribute     Justifies all text, dates, and numbers evenly against both sides of the field or text box.
Show Date Picker	Shows or hides a pop-up calendar control that appears when users click Date/Time fields. Default: For dates. Select Never to hide the control. Note: If you use an input mask for a Date/Time field, the Date Picker control is unavailable regardless of how you set this property.

Top of Page