The date and time data types have a wide variety of formats to help meet your unique circumstances. When formatting you have three choices: keep the default formats, apply a predefined format, or create a custom format. When you apply a format to a table field, that same format is automatically applied to any form or report control that you subsequently bind to that table field. Formatting only changes how the data is displayed and does not affect how the data is stored or how users can enter data.
In this article
Overview of date and time 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. where 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.2018 12:07:12 PM or 28/11/2018 12:07:12 PM. In the United States, you would see 11/28/2018 12:07:12 PM.
It is possible to change these predefined formats by using custom display formats. For example, you can enter a date in a European format such as 28.11.2018, and have the table, form, or report display the value as 11/28/2018. The custom format that you select will not affect how the data is entered or how Access stores that data.
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.2018, 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.
When you want to constrain entry of date and time in a specific format, use an input mask. 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. For more information, see Control data entry formats with input masks.
Apply a predefined format
Access provides several predefined formats for date and time data.
In a table
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time field that you want to format.
-
In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list.
-
After you select a format, the Property Update Options button 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.
For more information, see Propagate a field property.
-
Save your changes and switch to Datasheet view to see whether the format meets your needs.
Note New forms, reports, or views that you create based on this table get the table's formatting, but you can override this on the form, report, or view without changing the table's formatting.
In a form or report
-
Open the form or report Layout View or Design View.
-
Position the pointer in the text box with the date and time.
-
Press F4 to display the Property Sheet.
-
Set the Format property to one of the predefined date formats.
In a query
-
Open the query in Design View.
-
Right-click the date field, and then click Properties.
-
In the Property Sheet, select the format you want from the Format property list.
In an expression
-
Use the FormatDateTime function to format a date value into one of several predefined formats.
You might find this helpful if you are working in an area that requires an expression, such as a macro or a query.
Examples of predefined formats
| 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/2018 10:10:42 AM |
| Long Date | Displays only date values, as specified by the Long Date format in your Windows regional settings. | Monday, August 27, 2018 |
| Medium Date | Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings. | 27/Aug/18 |
| Short Date | Displays date values, as specified by the Short Date format in your Windows regional settings. | 8/27/2018 |
| 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
Sometimes, the Format property drop-down list in the Property Sheet doesn't contain the exact date format you want. You can create your own custom format by using a type of code that Access recognizes for date formatting.
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 space. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy h:mm:ss.
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time or Date/Time Extended field you want to format.
-
In the Field Properties section, select the General tab, click the cell next to the Format box and enter the specific characters based on your formatting needs.
-
After you enter a format, the Property Update Options button 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.
For more information, see Propagate a field property.
-
Save your changes, and then switch to Datasheet view to see whether the format meets your needs.
Examples of custom formats
Here are some examples of different ways to format January 13, 2012:
| To format the date like… | Type this code into the Format property box: |
|---|---|
| 2012-01-13 | yyyy-mm-dd |
| 2012 | yyyy |
| 13 Jan 2012 | dd mmm yyyy |
| 13.01.2012 | dd\.mm\.yyyy (Access adds the backslashes automatically) |
| Fri 1/13 | ddd m/d |
| 2/6 | ww/w |
| Jan 13, 2012 | mmm d", "yyyy |
Custom format characters
To create a custom format, use the following characters as placeholders and separators. Custom formats that are inconsistent with the date/time settings specified in Windows regional settings are ignored. For more information, see Change the Windows regional settings to modify the appearance of some data types.
Note There is no custom format character for the nanosecond portion of a Date/Time Extended data type.
| 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. (1 to 7) |
| ww | Displays a number that corresponds to the week of the year (1 to 53). |
| 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 for 0001-9999 depending on the date and time data type supported range. |
| 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 | Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate. |
| am/pm | Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate. |
| A/P | Twelve-hour clock with the uppercase letter "A" or "P", as appropriate. |
| a/p | Twelve-hour clock with the lowercase letter "a" or "p", as appropriate. |
| AMPM | Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows. |
| 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, |
Set a Date/Time format in an Access Web App
Access Web Apps display data in "views", which have text boxes much like forms in desktop databases. However, instead of a property sheet, you set formatting options by selecting the text box and then clicking the Format button that appears. Then, select the format you want from the Format list.
Access apps do not have custom date formats. You can create queries in Access apps, but there aren't options for formatting date columns within the query. All date formatting is controlled at the view level. Lastly, the FormatDateTime function isn't available in Access apps.
No comments:
Post a Comment