Format

Purpose

Formats a number, date, time, or string according to instructions contained in a format expression.

Arguments

Format[$]( expression[, fmt][, firstweekday][, firstweek] )

Format uses the following arguments:

expression
numeric or string expression to be formatted.
fmt
format expression - a string of display-format characters that specify how the expression is to be displayed. Different type format expressions cannot be mixed in a single fmt argument. You can also use the same named formats used by the Format property.
firwtweekday
Optional integer argument that specifies the first day of the week. It can be one of the following values: 0=Use the First Weekday setting in the Options dialog box, 1=Sunday (default) 2=Monday, 3=Tuesday, etc.
firstweek
optional integer that specifies the first week of the year. It can be one of the following values: 0=Use the First Week setting in the Options dialog box, 1=Start on January 1 (dafault), 2=Start with the first four-day week, 3=Start with the first full week.
Notes

Format returns a Variant; Format$ returns a String.

If fmt is omitted or is zero-length and expression is a numeric expression, Format[$] provides the same functionality as the Str[$] function by converting the numeric value to the appropriate return data type. Note that positive numbers converted to strings using Format[$] lack the leading space reserved for displaying the sign of the value, whereas those converted using Str[$] retain the leading space.

You can use any of the following symbols to create a format expression for numbers.

Symbol
Meaning
Null string
Display the number with no formatting.
0
Digit placeholder. Display a digit or a zero. If there is a digit in the expression being formatted in the position where the 0 appears in the format string, display it; otherwise, display a zero in that position.
If the number being formatted has fewer digits than there are zeros (on either side of the decimal) in the format expression, leading or trailing zeros are displayed. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, the number is rounded to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, the extra digits are displayed without modification.
#
Digit placeholder. Display either a digit or nothing. If there is a digit in the expression being formatted in the position where the # appears in the format string, display it; otherwise, display nothing in that position.
This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.
. (period)
Decimal placeholder. The decimal placeholder determines how many digits are displayed to the left and right of the decimal separator. If the format expression contains only number signs to the left of this symbol, numbers smaller than 1 begin with a decimal separator. If you want a leading zero to always be displayed with fractional numbers, use 0 as the first digit placeholder to the left of the decimal separator instead. The actual character used as a decimal placeholder in the formatted output depends on the Number Format specified in the International section of the Microsoft Windows Control Panel. For some countries, a comma is used as the decimal separator.
%
Percentage placeholder. The expression is multiplied by 100. The percent character (%) is inserted in the position where it appears in the format string.
, (comma)
Thousand separator. The thousand separator separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Standard use of the thousand separator is specified if the format contains a comma surrounded by digit placeholders (0 or #). Two adjacent commas or a comma immediately to the left of the decimal separator (whether or not a decimal is specified) means "scale the number by dividing it by 1000, rounding as needed." You can scale large numbers using this technique. For example, you can use the format string "##0,," to represent 100 million as 100. Numbers smaller than 1 million are displayed as 0. The actual character used as the thousand separator in the formatted output depends on the Number Format specified in the International section of the Control Panel. For some countries, a period is used as the thousand separator.
E- E+ e- e+
Scientific format. If the format expression contains at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits in the exponent. Use E- or e- to place a minus sign (-) next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign (+) next to positive exponents.
: (colon)
Time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator depends on the Time Format specified in the International section of the Control Panel.
/
Date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in the formatted output depends on the Date Format specified in the International section of the Control Panel.
- + $ ( ) space
Display a literal character. To display a character other than one of those listed, precede it with a backslash (\) or enclose it in double quotation marks (" ").
\
Display the next character in the format string. Many characters in the format expression have a special meaning and can't be displayed as literal characters unless they are preceded by a backslash. The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\).
Examples of characters that can't be displayed as literal characters are the date- and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !).
"ABC"
Display the string inside the double quotation marks. To include a string in fmt from within Access Basic, you must use Chr(34) to enclose the text (34 is the ANSI code for a double quotation mark). In property sheets, just type the double quotation character.
* (asterisk)
Display the next character as the fill character. Any empty space in a field is filled with the character following the asterisk.

Unless the fmt argument contains one of the named formats, a format expression for numbers can have one to four sections separated by semicolons (;).

If you use
The result is
One section only
The format applies to all values.
Two sections
The first section applies to positive values and zeros, the second to negative values.
Three sections
The first section applies to positive values, the second to negative values, and the third to zeros.
Four sections
The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null or Empty values.

The following example has two sections: The first defines the format for positive values and zeros; the second section defines the format for negative values.

"$#,##0;($#,##0)"

If you include semicolons with nothing between them, the missing section is printed using the format of the positive value. For example, the following format displays positive and negative values using the format in the first section and displays "Zero" if the value is zero.

"$#,##0;;\Z\e\r\o"

Note, however, that if you don't include the fourth section, Format returns a zero-length string for Null or Empty values.

The following table shows some sample formats for numbers. (These examples all assume that the Country specified in the International section of the Control Panel is the United States.) The first column contains the format strings. The other columns contain the output that results if the formatted data has the value given in the column headings.

Format (fmt)
Positive 5
Negative 5
Decimal .5
Null
Null string
5
-5
0.5

0
5
-5
1

0.00
5.00
-5.00
0.50

#,##0
5
-5
1

#,##0.00;;;Nil
5.00
-5.00
0.50
Nil
$#,##0;($#,##0)
$5
($5)
$1

$#,##0.00;($#,##0.00)
$5.00
($5.00)
$0.50

0%
500%
-500%
50%

0.00%
500.00%
-500.00%
50.00%

0.00E+00
5.00E+00
-5.00E+00
5.00E-01

0.00E-00
5.00E00
5.00E00
5.00E-01

You can format date/time serial numbers using date and time or number formats (because date/time serial numbers are stored as floating-point values).

You can use any of the following symbols to create a format expression for dates and times.

Symbol
Meaning
c
Display the date as ddddd and display the time as t t t t t, in that order. Only date information is displayed if there is no fractional part to the date serial number; only time information is displayed if there is no integer portion.
d
Display the day as a number without a leading zero (1-31).
dd
Display the day as a number with a leading zero (01-31).
ddd
Display the day as an abbreviation (Sun-Sat).
dddd
Display the day as a full name (Sunday-Saturday).
ddddd
Display a date serial number as a complete date (including day, month, and year) formatted according to the Short Date. Format setting in the International section of the Control Panel. The default Short Date Format is m/d/yy.
dddddd
Display a date serial number as a complete date (including day, month, and year) formatted according to the Long Date dd, yyyy. Format setting in the International section of the Control Panel. The default Long Date Format is mmmm.
w
Display the day of the week as a number (1-7).
ww
Display the week of the year as a number (1-54).
m
Display the month as a number without a leading zero (1-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mm
Display the month as a number with a leading zero (01-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mmm
Display the month as an abbreviation (Jan-Dec).
mmmm
Display the month as a full name (January-December).
q
Display the quarter of the year as a number (1-4).
y
Display the day of the year as a number (1-366).
yy
Display the year as a two-digit number (00-99).
yyyy
Display the year as a four-digit number (0100-9999).
h
Display the hour as a number without leading zeros (0-23).
hh
Display the hour as a number with leading zeros (00-23).
n
Display the minute as a number without leading zeros (0-59).
nn
Display the minute as a number with leading zeros (00-59).
s
Display the second as a number without leading zeros (0-59).
ss
Display the second as a number with leading zeros (00-59).
t t t t t
Display a time serial number as a complete time (including hour, minute, and second) formatted using the time separator defined by the Time Format in the International section of the Control Panel. A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:nn:ss.
AM/PM
Use the 12-hour clock and display an uppercase AM with any hour before noon; display an uppercase PM with any hour between noon and 11:59 PM.
am/pm
Use the 12-hour clock and display a lowercase am with any hour before noon; display a lowercase pm with any hour between noon and 11:59 PM.
A/P
Use the 12-hour clock and display an uppercase A with any hour before noon; display an uppercase P with any hour between noon and 11:59 PM.
a/p
Use the 12-hour clock and display a lowercase a with any hour before noon; display a lowercase p with any hour between noon and 11:59 PM.
AMPM
Use the 12-hour clock and display the contents of the 1159 string (s1159) in the WIN.INI file with any hour before noon; display the contents of the 2359 string (s2359) with any hour between noon and 11:59 PM. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as it exists in the WIN.INI file. The default format is AM/PM.

The following are examples of date and time formats.

Format
Display
m/d/yy
12/7/58
d-mmmm-yy
7-December-58
d-mmmm
7-December
mmmm-yy
December-58
hh:nn AM/PM
08:50 PM
h:nn:ss a/p
8:50:35 p
h:nn
20:50
h:nn:ss
20:50:35
m/d/yy h:nn
12/7/58 20:50

Strings can also be formatted with Format[$]. A format expression for strings can have one to three sections separated by a semicolon.

If you use
The result is
One section only
The format applies to all string data.
Two sections
The first section applies to string data, the second to Null values and zero-length strings.
Three sections
The first section applies to string data, the second to Null values, and the third to zero-length strings.

You can use any of the following symbols to create a format expression for strings.

Symbol	Meaning
@	Character placeholder. Display a character or a space. If there is a character in the string being formatted in the position where the @ appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an ! character in the format string. See the ! character later in this table.
&	Character placeholder. Display a character or nothing. If there is a character in the string being formatted in the position where the & appears, display it; otherwise, display nothing. Placeholders are filled from right to left unless there is an ! character in the format string. See the ! character later in this table.
<	Force lowercase. All characters are displayed in lowercase format.
>	Force uppercase. All characters are displayed in uppercase format.
!	Force placeholders to fill from left to right instead of right to left.

*expression*	numeric or string expression to be formatted.
*fmt*	format expression - a string of display-format characters that specify how the expression is to be displayed. Different type format expressions cannot be mixed in a single *fmt* argument. You can also use the same named formats used by the Format property.
*firwtweekday*	Optional integer argument that specifies the first day of the week. It can be one of the following values: 0=Use the First Weekday setting in the Options dialog box, 1=Sunday (default) 2=Monday, 3=Tuesday, etc.
*firstweek*	optional integer that specifies the first week of the year. It can be one of the following values: 0=Use the First Week setting in the Options dialog box, 1=Start on January 1 (dafault), 2=Start with the first four-day week, 3=Start with the first full week.

Symbol	Meaning
Null string	Display the number with no formatting.
0	Digit placeholder. Display a digit or a zero. If there is a digit in the expression being formatted in the position where the 0 appears in the format string, display it; otherwise, display a zero in that position. If the number being formatted has fewer digits than there are zeros (on either side of the decimal) in the format expression, leading or trailing zeros are displayed. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, the number is rounded to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, the extra digits are displayed without modification.
#	Digit placeholder. Display either a digit or nothing. If there is a digit in the expression being formatted in the position where the # appears in the format string, display it; otherwise, display nothing in that position. This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.
. (period)	Decimal placeholder. The decimal placeholder determines how many digits are displayed to the left and right of the decimal separator. If the format expression contains only number signs to the left of this symbol, numbers smaller than 1 begin with a decimal separator. If you want a leading zero to always be displayed with fractional numbers, use 0 as the first digit placeholder to the left of the decimal separator instead. The actual character used as a decimal placeholder in the formatted output depends on the Number Format specified in the International section of the Microsoft Windows Control Panel. For some countries, a comma is used as the decimal separator.
%	Percentage placeholder. The expression is multiplied by 100. The percent character (%) is inserted in the position where it appears in the format string.
, (comma)	Thousand separator. The thousand separator separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Standard use of the thousand separator is specified if the format contains a comma surrounded by digit placeholders (0 or #). Two adjacent commas or a comma immediately to the left of the decimal separator (whether or not a decimal is specified) means "scale the number by dividing it by 1000, rounding as needed." You can scale large numbers using this technique. For example, you can use the format string "##0,," to represent 100 million as 100. Numbers smaller than 1 million are displayed as 0. The actual character used as the thousand separator in the formatted output depends on the Number Format specified in the International section of the Control Panel. For some countries, a period is used as the thousand separator.
E- E+ e- e+	Scientific format. If the format expression contains at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits in the exponent. Use E- or e- to place a minus sign (-) next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign (+) next to positive exponents.
: (colon)	Time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator depends on the Time Format specified in the International section of the Control Panel.
/	Date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in the formatted output depends on the Date Format specified in the International section of the Control Panel.
- + $ ( ) space	Display a literal character. To display a character other than one of those listed, precede it with a backslash (\) or enclose it in double quotation marks (" ").
\	Display the next character in the format string. Many characters in the format expression have a special meaning and can't be displayed as literal characters unless they are preceded by a backslash. The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\). Examples of characters that can't be displayed as literal characters are the date- and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !).
"ABC"	Display the string inside the double quotation marks. To include a string in fmt from within Access Basic, you must use Chr(34) to enclose the text (34 is the ANSI code for a double quotation mark). In property sheets, just type the double quotation character.
* (asterisk)	Display the next character as the fill character. Any empty space in a field is filled with the character following the asterisk.

If you use	The result is
One section only	The format applies to all values.
Two sections	The first section applies to positive values and zeros, the second to negative values.
Three sections	The first section applies to positive values, the second to negative values, and the third to zeros.
Four sections	The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null or Empty values.

Format (fmt)	Positive 5	Negative 5	Decimal .5	Null
Null string	5	-5	0.5
0	5	-5	1
0.00	5.00	-5.00	0.50
#,##0	5	-5	1
#,##0.00;;;Nil	5.00	-5.00	0.50	Nil
$#,##0;($#,##0)	$5	($5)	$1
$#,##0.00;($#,##0.00)	$5.00	($5.00)	$0.50
0%	500%	-500%	50%
0.00%	500.00%	-500.00%	50.00%
0.00E+00	5.00E+00	-5.00E+00	5.00E-01
0.00E-00	5.00E00	5.00E00	5.00E-01

Symbol	Meaning
c	Display the date as ddddd and display the time as t t t t t, in that order. Only date information is displayed if there is no fractional part to the date serial number; only time information is displayed if there is no integer portion.
d	Display the day as a number without a leading zero (1-31).
dd	Display the day as a number with a leading zero (01-31).
ddd	Display the day as an abbreviation (Sun-Sat).
dddd	Display the day as a full name (Sunday-Saturday).
ddddd	Display a date serial number as a complete date (including day, month, and year) formatted according to the Short Date. Format setting in the International section of the Control Panel. The default Short Date Format is m/d/yy.
dddddd	Display a date serial number as a complete date (including day, month, and year) formatted according to the Long Date dd, yyyy. Format setting in the International section of the Control Panel. The default Long Date Format is mmmm.
w	Display the day of the week as a number (1-7).
ww	Display the week of the year as a number (1-54).
m	Display the month as a number without a leading zero (1-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mm	Display the month as a number with a leading zero (01-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mmm	Display the month as an abbreviation (Jan-Dec).
mmmm	Display the month as a full name (January-December).
q	Display the quarter of the year as a number (1-4).
y	Display the day of the year as a number (1-366).
yy	Display the year as a two-digit number (00-99).
yyyy	Display the year as a four-digit number (0100-9999).
h	Display the hour as a number without leading zeros (0-23).
hh	Display the hour as a number with leading zeros (00-23).
n	Display the minute as a number without leading zeros (0-59).
nn	Display the minute as a number with leading zeros (00-59).
s	Display the second as a number without leading zeros (0-59).
ss	Display the second as a number with leading zeros (00-59).
t t t t t	Display a time serial number as a complete time (including hour, minute, and second) formatted using the time separator defined by the Time Format in the International section of the Control Panel. A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:nn:ss.
AM/PM	Use the 12-hour clock and display an uppercase AM with any hour before noon; display an uppercase PM with any hour between noon and 11:59 PM.
am/pm	Use the 12-hour clock and display a lowercase am with any hour before noon; display a lowercase pm with any hour between noon and 11:59 PM.
A/P	Use the 12-hour clock and display an uppercase A with any hour before noon; display an uppercase P with any hour between noon and 11:59 PM.
a/p	Use the 12-hour clock and display a lowercase a with any hour before noon; display a lowercase p with any hour between noon and 11:59 PM.
AMPM	Use the 12-hour clock and display the contents of the 1159 string (s1159) in the WIN.INI file with any hour before noon; display the contents of the 2359 string (s2359) with any hour between noon and 11:59 PM. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as it exists in the WIN.INI file. The default format is AM/PM.

Format	Display
m/d/yy	12/7/58
d-mmmm-yy	7-December-58
d-mmmm	7-December
mmmm-yy	December-58
hh:nn AM/PM	08:50 PM
h:nn:ss a/p	8:50:35 p
h:nn	20:50
h:nn:ss	20:50:35
m/d/yy h:nn	12/7/58 20:50