| Format, Format$ Functions |
Named Arguments
No
Syntax
Format(expression[, format[, firstdayofweek[, _
firstweekofyear]]])expression
Use: Required
Data Type: String/Numeric
Any valid string or numeric expression.
format
Use: Optional
A valid named or user-defined format expression.
firstdayofweek
Use: Optional
Data Type: Numeric
A constant that specifies the first day of the week.
firstweekofyear
Use: Optional
Data Type: Numeric
A constant that specifies the first week of the year.
Return Value
A variant of subtype string containing the formatted expression.
Description
Allows you to use either predefined or user-defined formats to create an infinite variety of ways to output string, numeric, and date/time data. It's possibly the most complex single function call in VB.
Rules at a Glance
See CDate for an explanation of the firstdayofweek and firstweekofyear arguments.
format can be either a predefined or a user-defined format.
User-defined formats for numeric values are created with up to four sections. Each section is used for a different type of numeric value and is delimited with a semicolon. The four possible sections are shown in the following table:
Section Applies to 1 All values, if used alone; positive values, if used with more than one section 2 Negative values 3 Zero values 4 Null values It's not necessary to include all four sections in the format clause. However, the number of sections present determines what types of numeric values each section defines, as the following table shows:
# of Sections Applies to 1 All numeric values 2 Positive and zero values, negative values 3 Positive values, negative values, zero values 4 As shown in previous table If you leave a section blank, that section uses the same format as that defined for positive values. For example, the format string:
"#.00;;#,##"
means that negative values will appear in the same format as positive values.
Only one section is allowed where one of the named formats is used.
User-defined formats for string values can have two sections. The first is for all values, the second applies only to Null values or zero-length strings.
The predefined date and time formats are:
General Date
Example: Format("01/06/98","General Date")
Returns: 1/6/98
Long Date
Example: Format("01/06/98","Long Date")
Returns: Tuesday, January 06, 1998
Medium Date
Example: Format("01/06/98","Medium Date")
Returns: 06-Jan-98
Short Date
Example: Format("01/06/98","Short Date")
Returns: 1/6/98
Long Time
Example: Format("17:08:06","Long Time")
Returns: 5:08:06 P.M.
Medium Time
Example: Format("01/06/98","Medium Time")
Returns: 05:08 P.M.
Short Time
Example: Format("01/06/98","Short Time")
Returns: 17:08
The predefined numeric formats are:
General Number
Example: Format(562486.2356, "General Number")
Returns: 562486.2356
Currency
Example: Format(562486.2356, "Currency")
Returns: $562,486.24
Fixed
Example: Format(0.2, "Fixed")
Returns: 0.20
Standard
Example: Format(562486.2356, "Standard")
Returns: 562,486.24
Percent
Example: Format(.7521, "Percent")
Returns: 75.21%
Scientific
Example: Format(562486.2356, "Scientific")
Returns: 5.62E+05
Yes/No
Example #1: Format(0,"Yes/No")
Returns: No
Example #2: Format(23,"Yes/No")
Returns: Yes
True/False
Example #1: Format(0," True/False")
Returns: False
Example #2: Format(23," True/False")
Returns: True
On/Off
Example #1: Format(0," On/Off")
Returns: Off
Example #2: Format(23," On/Off")
Returns: On
Characters that create user-defined date and time formats are:
c
Element: Date
Display as: A date and/or time based on the short date and short time international settings of the current Windows system.
Example: Format("01/06/98 17:08:06", "c")
Returns: 1/6/98 5:08:06 PM
dddddd
Element: Date
Display as: A complete date based on the long date international setting of the current Windows system.
Example: Format("01/06/98", "dddddd")
Returns: Tuesday, January 06, 1998
(/ )
Element: Date separator
Display as: A date delimited with the specified character.
Example: Format("01/06/98", "mm-dd-yyyy")
Returns: 01-06-1998
d
Element: Day
Display as: A number (1–31) without a leading zero.
Example: Format("01/06/98", "d")
Returns: 6
dd
Element: Day
Display as: A number (01–31) with a leading zero.
Example: Format("01/06/98", "dd")
Returns: 06
ddd
Element: Day
Display as: An abbreviation (Sun–Sat).
Example: Format("01/06/98", "ddd")
Returns: Tue
dddd
Element: Day
Display as: A full name (Sunday–Saturday).
Example: Format("01/06/98", "dddd")
Returns: Tuesday
ddddd
Element: Date
Display as: A date based on the short date section in the computer's Windows international settings.
Example: Format("01/06/98", "ddddd")
Returns: 1/6/98
h
Element: Hour
Display as: A number (0–23) without leading zeros.
Example: Format("05:08:06", "h")
Returns: 5
hh
Element: Hour
Display as: A number (00–23) with leading zeros.
Example: Format("05:08:06", "hh")
Returns: 05
n
Element: Minute
Display as: A number (0–59) without leading zeros.
Example: Format("05:08:06", "n")
Returns: 8
nn
Element: Minute
Display as: A number (00–59) with leading zeros.
Example: Format("05:08:06", "nn")
Returns: 08
m
Element: Month
Display as: A number (1–12) without a leading zero.
Example: Format("01/06/98", "m")
Returns: 1
mm
Element: Month
Display as: A number (01–12) with a leading zero.
Example: Format("01/06/98", "mm")
Returns: 01
mmm
Element: Month
Display as: An abbreviation (Jan–Dec).
Example: Format("01/06/98", "mmm")
Returns: Jan
mmmm
Element: Month
Display as: A full month name (January–ecember).
Example: Format("01/06/98", "mmmm")
Returns: January
q
Element: Quarter
Display as: A number (1–4)
Example: Format("01/06/98", "q")
Returns: 1
s
Element: Second
Display as: A number (0–59) without leading zeros.
Example: Format("05:08:06", "s")
Returns: 6
ss
Element: Second
Display as: A number (00–59) with leading zeros.
Example: Format("05:08:06", "ss")
Returns: 06
ttttt
Element: Time
Display as: A time based on the 12-hour clock, using the time separator and leading zeros specified in Windows locale settings.
Example: Format("05:08:06", "ttttt")
Returns: 5:08:06 AM
AM/PM
Element: Time
Display as: A 12-hour clock format using uppercase A.M. and P.M.
Example: Format("17:08:06", "hh:mm:ss AM/PM")
Returns: 05:08:06 PM
am/pm
Element: Time
Display as: A 12-hour clock format using lowercase a.m. and p.m.
Example: Format("17:08:06", "hh:mm:ss am/pm")
Returns: 05:08:06 pm
A/P
Element: Time
Display as: A 12-hour clock format using an uppercase "A" for A.M. and "P" for P.M.
Example: Format("17:08:06", "hh:mm:ss A/P")
Returns: 05:08:06 P
a/p
Element: Time
Display as: A 12-hour clock format using a lowercase "a" for a.m. and "p" for p.m.
Example: Format("17:08:06", "hh:mm:ss a/p")
Returns: 05:08:06 p
(:)
Element: Time separator
Display as: A time format using a nonstandard character.
Example: Format("17:08:06", "hh::mm::ss")
Returns: 17::08::06
ww
Element: Week
Display as: A number (1–54).
Example: Format("01/06/98", "ww")
Returns: 2
w
Element: Weekday
Display as: A number (1 for Sunday through 7 for Saturday).
Example: Format("01/06/98", "w")
Returns: 3
y
Element: Day of Year
Display as: A number (1–366).
Example: Format("01/06/98", "y")
Returns: 6
yy
Element: Year
Display as: A two-digit number (00–99).
Example: Format("01/06/98", "yy")
Returns: 98
yyyy
Element: Year
Display as: A 4-digit number (100–9999).
Example: Format("01/06/98", "yyyy")
Returns: 1998
Characters that create user-defined number formats are as follows:
(0)
Description: Digit Placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise, a is displayed. The format definition dictates the number of digits after the decimal point, forcing the number held within an expression to be rounded to the given number of decimal places. It doesn't, however, affect the number of digits shown to the left of the decimal point.
Example #1: Format(23.675, "00.0000") returns 23.6750
Example #2: Format(23.675, "00.00") returns 23.68
Example #3: Format(2658, "00000") returns 02658
Example #4: Format(2658, "00.00") returns 2658.00
(#)
Description: Digit placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise, nothing is displayed.
Example #1: Format(23.675, "##.##") returns 23.68
Example #2: Format(23.675, "##.####") returns 23.675
Example #3: Format(12345.25, "#,###.##") returns 12,345.25
(.)
Description: Decimal placeholder. The actual character displayed as a decimal placeholder depends on the international settings of the local Windows system.
(%)
Description: Percentage placeholder. Displays expression as a percentage by first multiplying the value of expression by 100.
Example: Format(0.25, "##.00%") returns 25.00%
(,)
Description: Thousands separator. The actual character displayed as a thousands separator depends on the international settings of the local Windows system. You need to show only one thousands separator in your definition.
Example: Format(1000000, "#,###") returns 1,000,000
(E- E+ e- e+)
Description: 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 the letter "E" or "e" that was used in the format expression is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits displayed 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.
Example: Format(1.09837555, "######E-###") returns 109838E-5
- + $ ( )
Description: Displays a literal character
Example: Format(2345.25, "$#,###.##") returns $2,345.25
( )
Description: The character following the backslash is displayed as a literal character. Use the backslash to display a special formatting character as a literal.
Example: Format(0.25, "##.00 %") returns .25%
Note the difference between the result of this example and the result of the % formatting character.
Characters that create user-defined string formats:
@
Description: Character placeholder. If expression contains a character in the appropriate position, the character is displayed; otherwise, a space is displayed.
Example: Format("VBA", "*@*@@@@@") returns * * VBA
&
Description: Character placeholder. If expression contains a character in the appropriate position, the character is displayed; otherwise, nothing is displayed.
Example: Format("VBA", "*&&*&&&&") returns **VBA
<
Description: Displays all characters in lowercase.
Example: Format("VBA", "<") returns vba
>
Description: Displays all characters in uppercase.
Example: Format("vba", ">") returns VBA
!
Description: Processes placeholders from left to right (the default is to process from right to left).
Programming Tips and Gotchas
A little-known and important use of the Format function is to prevent an Invalid Use of Null error from occurring when assigning values from a recordset to a variable within your program. For example, if a field within either a DAO or RDO recordset created from either an Access or SQL Server database contains a Null value, you can trap this and change its value to " " as follows:
If IsNull(rsMyRecordSet!myValue) Then sMyString = "" Else sMyString = rsMyRecordSet!myValue End If
However, assigning the value returned by the Format function that has been passed the recordset field can do away with this long and tedious coding, as the following line of code illustrates:
sMyString = Format(rsMyRecordSet!myValue)
The Format function is a workaround when using the ! delimiter to assign the value of a field in an RDO recordset to an item of a collection. The following code shows the assignment of a recordset field to a collection element:
MyColl.Add rsMyRecordSet!myValue
When this recordset is closed, references to this item of this collection element results in an error. One of the simplest workarounds is to assign the return value of a Format function, like this:
MyColl.Add Format(rsMyRecordSet!myValue)
If you are passing a date to SQL Server, what date format should you use? By default, SQL Server expects an American date format, mmddyy, but the database may have been altered to accept other date formats, or you could be passing data to a stored procedure that begins with a date time conversion statement (SET DATEFORMAT dateformat). The only sure way of passing a date into SQL Server is by using the ANSI standard date format `yyyymmdd'—including the single quotation marks.
When passing a date to a Jet (Access) database, you should surround the date with hash characters (#); for example: #12/31/1999#.
Formatting numbers using Format without a format definition is also preferable to simply using the Str function. Unlike Str, the Format function removes the leading space normally reserved for the sign from positive numbers.
You can also use the Format function to scale numbers by 1000 by placing a thousands separator to the immediate left of the decimal point for each 1000 you wish the number to be scaled by. Thus:
' one separator divides the expression by 1000 = 1000 Format(1000000, "##0,.") 'two separators divides the expression by 1,000,000 = 1 Format(1000000, "##0,,.")
Visual Basic Version 6 introduces the concept of data binding, where a field in a data recordset can be programmatically bound to a property of a form, control, or other data consumer. Part of this new technology is the Format Object, which automatically formats data coming from the recordset based on properties you set within the object. Modified data passed back to the recordset is automatically unformatted.