One of the most powerful and complex functions, Format(), returns its argument in a different format from how the argument was passed. Here's the syntax of Format():
Format(expression, format)
Format() returns a Variant data type that you'll almost always use as a string. The expression can be any numeric or string expression. You can format all kinds of data-numbers, strings, dates, and times-to look differently. For example, you might want to print check amounts with commas and a dollar sign.
The format is a string variable or expression that contains one or more of the display-format characters shown in Tables 14.5 through 14.7. The table that you use depends on the kind of data (string, numeric, or date) that you want to format. The tables are long, but after looking at a few examples, you'll learn how to use the display-format characters.
Table 14.5 The String Display-Format Characters
Symbol | Description |
@ | A character appears in the output at the @ position. If there's no character at the @'s position in the string, a blank appears. The @ fills (if there are more than one) from right to left. |
& | This character is just like @, except that nothing appears if no character at the &'s position appears in the string being printed. |
! | The exclamation point forces all placeholder characters (the @ and &) to fill from left to right. |
< | Less-than forces all characters to lowercase. |
> | Greater-than forces all characters to uppercase. |
Table 14.6 The Numeric Display-Format Characters
Symbol | Description |
Null string, "" | This string displays the number without formatting. |
0 | A digit appears in the output at the 0 position if a digit appears in the number being formatted. If no digit is at the 0's position, a 0 appears. If not as many zeros in the number are being formatted as there are zeros in the format field, leading or trailing zeros print. If the number contains more numeric positions, the 0 forces all digits to the right of the decimal point to round to the display-format's pattern and all digits to the left print as is. You mostly use this display-format character to print leading or trailing zeros when you want them. |
# | The pound-sign character works like 0, except that nothing appears if the number being formatted doesn't have as many digits as the display-format has #s. |
. | The period specifies how many digits (by its placement within 0 or #s) are to appear to the left and right of a decimal point. |
% | The number being formatted is multiplied by 100, and the percent sign (%) is printed at its position inside the display-format string. |
, | If a comma appears among 0s or #s, the thousands are easier to read because the comma groups every three places in the number (unless the number is below 1,000). If you put two commas together, you request that the number be divided by 1,000 (to scale down the number). |
E-, E+, e-, e+ | The number is formatted into scientific notation if the format also contains at least one 0 or #. |
: | The colon causes colons to appear between a time's hour, minute, and second values. |
/ | The slash ensures that slashes are printed between a date's day, month, and year values. |
-, +, $, space | All these characters appear as is in their position within the formatted string. |
\ | Whatever character follows the backslash appears at its position in the formatted string. |
Table 14.7 The Date Display-Format Characters
Symbol | Description |
c | Displays either the date (just like the ddddd symbol if only a date appears), the time (just like ttttt if only a time appears), or both if both values are present. |
d | Displays the day number from 1 to 31. |
dd | Displays the day number with a leading zero from 01 to 31. |
ddd | Displays an abbreviated three-character day from Sun to Sat. |
dddd | Displays the full day name from Sunday to Saturday. |
ddddd | Displays the date (month, day, year) according to your settings in the International section of your Control Panel's Short Date format (usually m/d/yy). |
dddddd | Displays the date (month, day, year) according to your settings in the International section of your Control Panel's Long Date format (usually mmmm dd, yyyy). |
w, ww | See Table 14.4. |
m | Displays the month number from 1 to 12. The m also means minute if it follows an h or hh. |
mm | Displays the month number with a leading zero from 01 to 12. The mm also means minute if it follows an h or hh. |
mmm | Displays the abbreviated month name from Jan to Dec. |
mmmm | Displays the full month name from January to December. |
q | Displays the quarter of the year. |
y | Displays the year's day number from 1 to 366. |
yy | Displays the two-digit year from 00 to 99 (when the year 2000 hits, yy still returns only the 2-digit year). |
yyyy | Displays the full year number from 1000 to 9999. |
h, n, s | See Table 14.4. |
ttttt | Displays the time according to your settings in the International section of your Control Panel's Time format (usually h:nn:ss). |
AMPM | Uses the 12-hour clock time and displays AM or PM. |
ampm | Uses the 12-hour clock time and displays am or pm. |
AP | Uses the 12-hour clock time and displays A or P. |
ap | Uses the 12-hour clock time and displays a or p. |
The following statements demonstrate the string display-format characters. The remarks to the right of each statement explain that the target variable (the variable on the left of the equal sign) is receiving formatted data.
strS = Format("AbcDef", ">") ' ABCDEF is assigned strS = Format("AbcDef", "<") ' abcdef is assigned strS = Format("2325551212", "(@@@) @@@-@@@@") ' (232) 555-1212
As the last statement shows, you can put string data into the format you prefer. If the data to be formatted, such as the phone number in the last line, is a string variable from a table's text field, the Format() statement works just the same.
Suppose that it's possible to leave out the area code of the phone number that you want to print. Format() fills from right to left, so the statement
strS = Format("5551212", "(@@@) @@@-@@@@")
stores the following in strS:
( ) 555-1212
If you had included the area code, it would have printed inside the parentheses.
Only use the ! when you want the fill to take place from the other direction (when data at the end of the string being formatted might be missing). The statement
strS = Format("5551212", "!(@@@) @@@-@@@@")
incorrectly stores the following in strS:
(555) 121-2