Fields (in a table or query) and controls (on a form or report) all have a “Format” property that can be set to display data exactly how you desire. I will refer to the value in the format property as the “format code,” which is composed of defined symbols and/or literal characters enclosed in quotation marks.
FORMAT SYMBOLS
A format symbol is one or more characters used to represent a specific piece of information or delimiter or to indicate something else, such as alignment or color. Table 1 shows the results of format symbols using the same date and time example. As you might expect, “y” corresponds to the year in format code—for example, yy displays the last two digits of the year, and y will display the number of the day of the year. There’s also “m” for month, “d” for day, “h” for hour, and “s” for second, while “n” is used to represent minute to distinguish it from “m” for month.
The number of placeholder letters you include in the format code determines behavior. As mentioned, yy displays a two-digit year; yyyy in the format code will display a four-digit year. For both months and days, one letter in the code will display the month or day number. If you include two letters (e.g., mm), then a preceding 0 will be added if the number is less than 10. Three letters will display the name abbreviation, and four letters is the full name.
For time components, including two letters also displays a preceding zero if the number is less than 10. If you include any of AM/PM, am/pm, A/P, or a/p in the format code, then hours will range from 1-12 instead of 0-23.
The official separators, or delimiters, for time and date when numbers are entered in format code are colon (:) for time and slash (/) for date, such as in 8:30:19 and 7/4/18. Additionally, a dash (-) is often recognized as a date delimiter.
LITERAL CHARACTERS
If you want to include static characters that don’t change, enclose them in double quotation marks. As you type, you can also preface a static character with a backslash, such as Q to display the letter Q. After you complete the entry, Access will add the quotation marks. For example, type Qq to display the current quarter, i.e., Q1, Q2, Q3, or Q4. After you complete the entry, however, Access will adjust the format code to "Q"q.
Format symbols and literal characters can be strung together to indicate how you want to see the information. For example, if you want to display which week of the year and day of the week a value represents, such as “Week 27, Day 4” or “Week 32, Day 2,” then use format code "Week "ww", Day "d. Internally, the value hasn’t changed, only the way you see it.
If you want to display time using periods instead of colons between each number, use h.n.s in the format code. Access will interpret what is typed and then change the entry to h"."n"."s.
NAMED FORMATS
Rather than having to construct common formats from symbols, there also are a number of predefined (or named) formats that display according to your Windows Regions settings. Table 2 shows examples. You can simply enter the format name in the property rather than enter the symbols. If you work internationally, Medium Date is good to use as it eliminates ambiguity about which values indicate the year, month, and day. The General Date format will show date and time when there is data, with the exception of displaying the date for 12/30/1899 (because the serial date=0). Then it will just show time.
Download this month’s database: SF_1807_DateFormat.
SF SAYS
With the Format property, you have a lot of flexibility in how date and time values are displayed in your database.
July 2018
