Add and customize date and time formats
This article describes how Microsoft Office Access stores date and time information, how you can add a date and time fields to your tables, forms, and reports, and how you can customize the date and time format to suit your needs.
What do you want to do?
Understand date and time formats
A date and time can be entered in a database, and then displayed in several formats. Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. were mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds.
These automatic formats for dates and times vary depending on the geographic location specified in the Microsoft Windows Regional and Language Options setting on your computer. For example, in Europe and many parts of Asia, depending on your location, you might see the date and time as 28.11.2006 12:07:12 PM or 28/11/2006 12:07:12 PM. In the United States, you would see 11/28/2006 12:07:12 PM. For more information about how to change your Regional and Language Options setting, see Change the Windows regional settings to change how dates and times are displayed, later in this article.
It is possible to change these automatic formats by using custom display formats. However, the display format that you select will not affect how the data is entered or how Access stores that data. For example, you can enter a date in a European format such as 28.11.2006, and have the table, form, or report display the value as 11/28/2006.
Access automatically provides a certain level of data validation related to date and time formatting. For example, If you enter an invalid date, such as 32.11.2006, a message appears and you would have the option of entering a new value or converting the field from the Date/Time data type to the Text data type. 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 to .9999, or 23:59:59.
Add a Date/Time field
To use dates and times in a database, you start by creating a Date/Time field in one or more of your database tables. The settings for date or time fields always resides in a table, but the date or time value can be displayed in a table, form, or report.
-
In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
-
In the upper section of the table designer, select a blank row.
-
In the Field Name column, type a name for the new field.
-
In the Data Type column, select Date/Time from the list, and then save the table.
The new field should resemble this, although the field name can vary:
You can also store the date and time values as numbers when you plan to perform calculations on the date and time data. For example, you can calculate a total number of hours worked (a time card), or the age of an invoice. For more information about how to calculate date values, see the article on the Date function.
Format a Date/Time field
Access provides several predefined formats for the date and time data, but if these formats do not meet your needs you can specify a custom format. If you do not select either a predefined or custom format, Access automatically applies the General Date format — m/dd/yyyy h:mm:ss AM/PM.
Apply a predefined format
-
In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
-
In the upper section of the design grid, select the Date/Time field that you want to format.
-
In the lower section, click the Format property box, and then select a format from the drop-down list.
-
After you select a format, the Property Update Options smart tag
appears, and lets you to apply your new format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field. -
To apply your changes to the entire database, when the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes.
-
Save your changes and switch to Datasheet view to see whether the format meets your needs.
Note: Your Windows regional settings may control part or all of how a predefined format is displayed.
Table of predefined formats and examples
| Format | Description | Example |
| General Date | (Default) Displays date values as numbers and time values as hours, minutes, and seconds followed by AM or PM. For both types of values, Access uses the date and time separators specified in your Windows regional settings. If the value does not have a time component, Access displays only the date. If the value has no date component, Access displays only the time. | 06/30/2008 10:10:42 AM |
| Long Date | Displays only date values, as specified by the Long Date format in your Windows regional settings. | Monday, August 29, 2006 |
| Medium Date | Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings. | 29/Aug/06 |
| Short Date | Displays date values, as specified by the Short Date format in your Windows regional settings. | 8/29/2006 |
| Long Time | Displays hours, minutes, and seconds followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings. | 10:10:42 AM |
| Medium Time | Displays hours and minutes followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings. | 10:10 AM |
| Short Time | Displays only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings. | 10:10 |
Apply a custom format
When you apply a custom format to the Date/Time field, you can combine different formats by having two sections, one for the date and another for the time. In such an instance, you would separate the sections with a semicolon. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy;h:mm:ss.
Tip: In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
-
In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
-
In the upper section of the design grid, select the Date/Time field you want to format.
-
In the lower section, click the Format property box, and then enter your format.
Table of placeholders and separators for a custom format
| Character | Description |
| Date separator | Controls where Access places the separator for days, months, and years. Use the separator defined in the Windows regional settings. For example, in English (U.S.), use a slash (/). |
| Time separator | Controls where Access places separators for hours, minutes, and seconds. Use the separator defined in the Windows regional settings. For example, in English (U.S.), use a colon (:) . |
| c | Displays the general date format. |
| d or dd | Displays the day of the month as one or two digits. For one digit, use a single placeholder; for two digits, use two placeholders. |
| ddd | Abbreviates the day of the week to three-letters. |
| dddd | Spells out all days of the week. |
| ddddd | Displays the Short Date format. |
| dddddd | Displays the Long Date format. |
| w | Displays a number that corresponds to the day of the week. |
| m or mm | Displays the month as either a one-digit or two-digit number. |
| mmm | Abbreviates the name of the month to three-letters. For example, January appears as Jan. |
| mmmm | Spells out all month names. |
| q | Displays the number of the current calendar quarter (1-4). For example, if you hire a worker in May, Access will display 2 as the quarter value. |
| y | Displays the day of the year, 1-366. |
| yy | Displays the last two digits of the year. Note: We recommend entering and display all four digits of a given year. |
| yyyy | Displays all digits in a year in the range 0100-9999. |
| Time separator | Controls where Access places the separator for hours, minutes, and seconds. Use the separator defined in the Windows regional settings. For information about those settings, see the section, Change the Windows regional settings to change how dates and times are displayed. |
| h or hh | Displays the hour as one or two digits. |
| n or nn | Displays minutes as one or two digits. |
| s or ss | Displays seconds as one or two digits. |
| tttt | Displays the Long Time format. |
| AM/PM | Displays 12-hour clock values with a trailing AM or PM. Access relies on the system clock in your computer to set the value. |
| A/P or a/p | Displays 12-hour clock values with a trailing A, P, a, or p. Access relies on the system clock in your computer to set the value. |
| AMPM | Displays 12-hour clock values, but uses the morning and afternoon indicators specified in the Windows regional settings. For information about those settings, see the section, Change the Windows regional settings to change how dates and times are displayed. |
| Blank space, + - $ () | Use blank spaces, some math characters (+ -), and financial symbols ($ ¥ £) as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), you must surround them in double quotation marks. |
| "Literal text" | Surround any text that you want users to see in double quotes. |
| \ | Forces Access to display the character that immediately follows. This is the same as surrounding a character in double quotation marks. |
| * | When used, the character immediately after the asterisk becomes a fill character — a character used to fill blank spaces. Access normally displays text as left aligned and fills any area to the right of the value with blank spaces. You can add fill characters anywhere in a format string, and Access will fill any blank spaces with the specified character. |
| [color] | Applies a color to all of the values in a section of your format. You must enclose the name in brackets and use one of the following names: black, blue, cyan, green, magenta, red, yellow, or white. For example, dddd[Pink] would display the day in Pink color |
After you enter a format, the Property Update Options smart tag appears and lets you apply the format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field.
If you choose to apply your changes to the entire database, the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes to apply the format.
Save your changes, and then switch to Datasheet view to see whether the format meets your needs.
Add a date by using the Date Picker
There are several ways to add a date to your table. If it is available, using the Date Picker control is a quick option.
Note: The Date Picker control is not available if an input mask is applied to the Date/Time field.
-
Click the field that you want to add a date to. If the Date Picker control is available, a calendar icon appears.
-
Click the calendar icon. A calendar control appears.
-
Do one of the following:
-
To enter the current date, click Today.
-
To select a day in the current month, click the date.
-
To select a different month and day, use the forward or back buttons.
-
Specify a format for entering date and time data with an input mask
Use an input mask when you want to make sure that users enter date and time data in a specific format. For example, if you applied an input mask to enter dates in the European format, someone entering data in your database will not be able to enter dates in other formats. Input masks can be applied to fields in tables, query result sets, and to controls on forms and reports.
You can define an input mask in one format but set a different display format for the same data. For example, the input mask can require users to enter the date as YYYY.MM.DD, and then set the format for displaying the date as, DD-MMM-YYYY, so that the date appears as 24-July-2008.
For more information about input masks, see the article Create an input mask to enter field or control values in a specific format.
Add an input mask
-
In the Navigation Pane, right-click the table that you want to work with, and then click Design View.
-
If the table does not contain a Text field, in the upper section of the design grid, select a blank row, click the Data Type column, select Text, and then save your changes.
-
Select the Date/Time field, and then in the lower section of the design grid, on the General tab, click the Input Mask property.
-
Note: Input masks contain three sections, all separated by semicolons. The first section is mandatory, and the rest are optional. The first section defines the mask string and consists of placeholder and literal characters. The second section defines whether you want to store the mask characters plus the data in the field. Enter 0 if you want to store the mask and your data; enter 1 if you only want to store the data. The third section defines the placeholder used to indicate a position for data. By default, Access uses the underscore (_). If you want to use another character, you can enter it in the desired position in the mask string, or you can enter it in the third section of your mask. Also, a position in an input mask accepts only one character or space.
-
-
In the Input Mask property box, type 00 LLL 0000;0;_, and then press ENTER.
-
Access adds several characters to your input mask, so that it appears as follows: 00\LLL\0000;0;_
-
-
After you enter the mask, the Property Update Options smart tag appears and lets you apply the mask to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag
, and then click Update Input Mask everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field. -
If you choose to apply your changes to the entire database, the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format.
-
To apply the format, click Yes, and then click Save.
Change the Windows regional settings to change how dates and times are displayed
Windows supports a variety of languages, plus the date/time formats for the countries and regions that use those languages. The date/time formats in your regional settings affect part or all of the predefined and custom display formats that you apply to your Access data. For example, if you enter a date value and use the forward slash as the date separator (8/29/2006), and then apply the General Date display format to the Date/Time field, when you view your data, you may or may not see the forward slashes, depending on the character that is defined by your regional settings.
Access uses the date and time separators, financial symbols, and other characters specified in the Windows regional settings unless you override them with a custom display format.
The following steps explain how to set and change your regional settings.
In Windows Vista
-
Click the Start button
, and then click Control Panel. -
Double-click Clock, Language, and Region.
Note: If you use Classic view, double-click Regional and Language Options.
The Regional and Language Options dialog box appears.
-
Click the Formats tab.
-
Under Current format, click Customize this format.
The Customize Regional Options dialog box appears.
-
Click the tab that contains the settings that you want to modify, and then make your changes.
In Microsoft Windows XP (Classic view)
-
On the Windows task bar, click Start, and then click Control Panel.
-
In Control Panel, double-click Regional and Language Options.
The Regional and Language Options dialog box appears.
-
Click the Regional Options tab.
-
Under Standards and formats, click Customize.
The Customize Regional Options dialog box appears.
-
Click the tab that contains the settings that you want to modify, and then make your changes.
In Microsoft Windows XP (Category view)
-
On the Windows task bar, click Start, and then click Control Panel.
Control Panel appears.
-
Click Date, Time, Language, and Regional Options.
The Date, Time, Language, and Regional Options dialog box appears.
-
Click Change the format of numbers, dates, and times.
The Regional and Language Options dialog box appears.
-
Under Standards and Formats, click Customize.
The Customize Regional Options dialog box appears.
-
Click the tab that contains the settings that you want to modify, and then make your changes.
No comments:
Post a Comment