Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You add a Date/Time or Date/Time Extended 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 and 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 and time fields store the dates and times in the following ways:

Date/Time

Date/Time uses 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.

Date/Time Extended

Date/Time Extended stores dates and times in an encoded string of 42 bytes. A date is stored in an unsigned long type that supports a range from 1-1-1 AD to 9999 AD. Time is stored in an unsigned long long type based on the following formula:

= (datehour * 3600 + dateminute * 60 + datesecond) * PowerOfTen(TimeScale) + fractionalSeconds

For more information, see Using the Date/Time Extended data type.

Top of Page

Add a date and 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 or Date/Time Extended 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 or Date/Time Extended from the list.

  6. Save your changes.

Top of Page

Add a date and 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 or Date/Time Extended 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 or Date/Time Extended from the list.

  6. Save your changes.

Top of Page

Set date and 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 and time field

You can use Datasheet view or Design view to delete a Date/Time or Date/Time Extended field from a table.

Warning    When you delete a Date/Time or Date/Time Extended 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 or Date/Time Extended 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 or Date/Time Extended 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 and 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.

Decimal Places (Date/Time Extended only)

Enter a fractional precision to specify the number of digits to the right of the decimal point (1-7).

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×