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.
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.
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
-
Open the table in Datasheet view
-
If necessary, scroll horizontally to the first blank field.
-
In the Field Name column, select the first blank row, and then type a name for the field.
-
Select the adjacent cell in the Data Type column, and then select Date/Time or Date/Time Extended from the list.
-
Save your changes.
Add a Date/Time field to a new table
-
On the Create tab, in the Tables group, click Table.
-
Click Save
-
Right-click the document tab for the new table and click Design View.
-
In the Field Name column, select the first blank row, and then type a name for the field.
-
Select the adjacent cell in the Data Type column, and then select Date/Time or Date/Time Extended from the list.
-
Save your changes.
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
-
Open the table inDesign View.
-
In the Field Name column, select the first blank row, and then type a name for the field.
-
Select the adjacent cell in the Data Type column, and then select Date/Time or Date/Time Extended from the list.
-
Save your changes.
Add to a new table
-
On the Create tab, in the Tables group, click Table.
-
Click Save , and in the Save As dialog box, enter a name for the new table.
-
Right-click the document tab for the new table and click Design View.
-
In the Field Name column, select the first blank row, and then type a name for the field.
-
Select the adjacent cell in the Data Type column, and then select Date/Time or Date/Time Extended from the list.
-
Save your changes.
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.
-
On the General tab in the lower section of the table designer, under Field Properties, locate the property that you want to change.
-
Select the field next to the property name.
-
Select the field next to the property name. Depending on the property, you can enter data, start the Expression Builder by clicking
For information about how to use each field property, select the property and then press F1.
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
-
Open the table in Datasheet View.
-
Locate the Date/Time or Date/Time Extended field, right-click the header row (the name), and click Delete Field.
-
Click Yes to confirm the deletion.
Delete in Design view
-
Open the table in Design View.
-
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.
-
Click Yes to confirm the deletion.
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 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:
|
|
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. |
|
Text Align |
Specifies the alignment for data in a Date/Time field. Choices:
|
|
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. |
