Format Function |
Microsoft.VisualBasic.Strings
Format( expression [, style [, dayofweek [, _ weekofyear ]]])
Any valid string or numeric expression
A valid named or user -defined format expression
A constant that specifies the first day of the week
A constant that specifies the first week of the year
Constant | Value | Description |
---|---|---|
System |
| NLS API setting |
Sunday | 1 | Sunday (default) |
Monday | 2 | Monday |
Tuesday | 3 | Tuesday |
Wednesday | 4 | Wednesday |
Thursday | 5 | Thursday |
Friday | 6 | Friday |
Saturday | 7 | Saturday |
Constant | Value | Description |
---|---|---|
UseSystemDayOfWeek |
| Use the NLS API setting. |
FirstJan1 | 1 | Start with the week in which January 1 occurs (default). |
FirstFourDays | 2 | Start with the first week that has at least four days in the new year. |
FirstFullWeek | 3 | Start with first full week of the year. |
A string containing the formatted expression
Allows you to use either predefined or user-defined formats to output string, numeric, and date/time data
style can be either a predefined or user-defined format.
User-defined formats for numeric values are created with up to four sections, delimited by semicolons. Each section is used for a different type of numeric value. 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 | Nothing value |
It is not necessary to include all four sections in the style clause. However, the number of sections present determines what types of numeric values each section defines, as the following table shows:
Number 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, it will use 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:
Format | Example | Returns |
---|---|---|
General Date | Format("01/06/98","General Date") | 1/6/98 |
Long Date | Format("01/06/98","Long Date") | Tuesday, January 06, 1998 |
Medium Date | Format("01/06/98","Medium Date") | 06-Jan-98 |
Short Date | Format("01/06/98","Short Date") | 1/6/98 |
Long Time | Format("17:08:06","Long Time") | 5:08:06 PM |
Medium Time | Format("17:08:06","Medium Time") | 05:08 PM |
Short Time | Format("17:08:06","Short Time") | 17:08 |
The predefined numeric formats are:
Format | Examples | Returns |
---|---|---|
General Number | Format(562486.2356, "General Number") | 562486.2356 |
Currency | Format(562486.2356, "Currency") | $562,486.24 |
Fixed | Format(0.2, "Fixed") | 0.20 |
Standard | Format(562486.2356, "Standard") | 562,486.24 |
Percent | Format(.7521, "Percent") | 75.21% |
Scientific | Format(562486.2356, "Scientific") | 5.62E+05 |
Yes/No | Format(0,"Yes/No") Format(23,"Yes/No") | No Yes |
True/False | Format(0,"True/False") Format(23,"True/False") | False True |
On/Off | Format(0,"On/Off") Format(23,"On/Off") | Off On |
Characters used to create user-defined date and time formats are:
Char | Element Used In | Display As | Example | Returns |
---|---|---|---|---|
c | Date | A date and/or time based on the short-date and short-time international settings of the current Windows system | Format("01/06/98 17:08:06", "c") | 1/6/98 5:08:06 PM |
dddddd | Date | A complete date based on the long-date international setting of the current Windows system | Format("01/06/98", "dddddd") | Tuesday, January 06, 1998 |
( / ) | Dateseparator | A date delimited with the specified character | Format("01/06/98", "mm-dd-yyyy") | 01-06-1998 |
d | Day | A number (1-31) without a leading zero | Format("01/06/98", "d") | 6 |
dd | Day | A number (01-31) with a leading zero | Format("01/06/98", "dd") | 06 |
ddd | Day | An abbreviation (Sun-Sat) | Format("01/06/98", "ddd") | Tue |
dddd | Day | A full name (Sunday-Saturday) | Format("01/06/98", "dddd") | Tuesday |
ddddd | Date | A date based on the short date section in the computer's Windows international settings | Format("01/06/98", "ddddd") | 1/6/98 |
h | Hour | A number (0-23) without leading zeros | Format("05:08:06", "h") | 5 |
hh | Hour | A number (00-23) with leading zeros | Format("05:08:06", "hh") | 05 |
n | Minute | A number (0-59) without leading zeros | Format("05:08:06", "n") | 8 |
nn | Minute | A number (00-59) with leading zeros | Format("05:08:06", "nn") | 08 |
m | Month | A number (1-12) without a leading zero | Format("01/06/98", "m") | 1 |
mm | Month | A number (01-12) with a leading zero | Format("01/06/98", "mm") | 01 |
mmm | Month | An abbreviation (Jan-Dec) | Format("01/06/98", "mmm") | Jan |
mmmm | Month | A full month name (January-December) | Format("01/06/98", "mmmm") | January |
q | Quarter | A number (1-4) | Format("01/06/98", "q") | 1 |
s | Second | A number (0-59) without leading zeros | Format("05:08:06", "s") | 6 |
ss | Second | A number (00-59) with leading zeros | Format("05:08:06", "ss") | 06 |
ttttt | Time | A time based on the 12-hour clock, using the time separator and leading zeros specified in Windows locale settings | Format("05:08:06", "ttttt") | 5:08:06 AM |
AM/PM | Time | A 12-hour clock format using uppercase AM and PM | Format("17:08:06", "hh:mm:ss AM/PM") | 05:08:06 PM |
am/pm | Time | A 12-hour clock format using lowercase am and pm | Format("17:08:06", "hh:mm:ss am/pm") | 05:08:06 pm |
A/P | Time | A 12-hour clock format using an uppercase "A" for AM and "P" for PM | Format("17:08:06", "hh:mm:ss A/P") | 05:08:06 P |
a/p | Time | A 12-hour clock format using a lowercase "a" for AM and "p" for PM | Format("17:08:06", "hh:mm:ss a/p") | 05:08:06 p |
( : ) | Time separator | A time format using a nonstandard character | Format("17:08:06", "hh::mm::ss") | 17::08::06 |
ww | Week | A number (1 - 54) | Format("01/06/98", "ww") | 2 |
w | Weekday | A number (1 for Sunday through 7 for Saturday) | Format("01/06/98", "w") | 3 |
y | Day of Year | A number (1 - 366) | Format("01/06/98", "y") | 6 |
yy | Year | A 2-digit number (00 - 99) | Format("01/06/98", "yy") | 98 |
yyyy | Year | A 4-digit number (100 - 9999) | Format("01/06/98", "yyyy") | 1998 |
Characters used to create user-defined number formats are as follows :
Char | Description | Examples |
---|---|---|
( ) | Digit placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise , a 0 will be 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 does not, however, affect the number of digits shown to the left of the decimal point. | Format(23.675, "00.0000") returns 23.6750 Format(23.675, "00.00") returns 23.68 Format(2658, "00000") returns 02658 Format(2658, "00.00") returns 2658.00 |
( # ) | Digit placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise, nothing will be displayed. | Format(23.675, "##.##") returns 23.68 Format(23.675, "##.####") returns 23.675 Format(12345.25, "#,###.##") returns 12,345.25 |
( . ) | Decimal placeholder. The actual character displayed as a decimal placeholder depends on the international settings of the local Windows system. | |
(%) | Percentage placeholder. Displays expression as a percentage by first multiplying the value of expression by 100. | Format(0.25, "##.00%") returns 25.00% |
( , ) | Thousands separator. The actual character displayed as a thousands separator depends on the international settings of the local Windows system. You only need to show one thousands separator in your definition. | Format(1000000, "#,###") returns 1,000,000 |
( E- E+ e- e+ ) | Scientific format. If the format expression contains at least one digit placeholder ( 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. | |
- + $ ( ) | Displays a literal character. | Format(2345.25, "$#,###.##") returns $2,345.25 |
( \ ) | The character following the backslash will be displayed as a literal character. Use the backslash to display a special formatting character as a literal. | Format(0.25, "##.00\%") returns .25% Note the difference between the result of this example and the result of the % formatting character. |
A little known and very 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 could 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)
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 it is possible for the database to 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. This is achieved 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,,.")
The VB 6 version of the Format function defined five special symbols (@, &, <, >, and !) for creating user-defined string formats. In VB.NET, these symbols are treated as literal characters.
FormatCurrency, FormatNumber, FormatPercent Functions, FormatDateTime Function