Create or delete a Date/Time field

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.

In this article

Understand Date/Time fields

Add a Date/Time field in Datasheet View

Add a Date/Time field in Design view

Set Date/Time field properties

Delete a Date/Time field

Date/Time field property reference

Understand Date/Time fields

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.

Double-precision number

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.

Top of Page

Add a Date/Time field in Datasheet View

You can add a Date/Time field to a new or existing table in Datasheet View.

Add to an existing table

  1. Open the table in Datasheet view

  2. If necessary, scroll horizontally to the first blank field.

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

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

  5. Save your changes.

Add a Date/Time field to a new table

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

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

  3. Right-click the document tab for the new table and 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.

Top of Page

Add a Date/Time field in Design view

You can add a Date/Time field to a new or existing table in Design View.

Add to an existing table

  1. Open the table inDesign View.

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

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

  4. Save your changes.

Add to a new table

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

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

  3. Right-click the document tab for the new table and 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.

Top of Page

Set Date/Time field properties

When you use Design view to add a Date/Time field to a table, you can set and change all properties for the field.

  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.

  3. Select the field next to the property name. Depending on the property, you can enter data, start the Expression Builder by clicking Builder button , or 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.

Warning    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 in Datasheet view

  1. Open the table in Datasheet View.

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

  3. Click Yes to confirm the deletion.

Delete in Design view

  1. Open the table in Design View.

  2. 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.

  3. Click Yes to confirm the deletion.

Top of Page

Date/Time field property reference

This table lists the Date/Time field properties and describes what each one does.

Property

Usage

Format

You enter custom formatting characters to define a display format. Formats defined here appear in datasheets, forms, and reports.

Input Mask

You enter an input mask string, or click Builder button to start the Input Mask Wizard.

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

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

In Access 2010 only, you can 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 Builder button 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.

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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×