Format Function
Description
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
Syntax
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
The Format function syntax has these parts
| Part | Description |
|
| expression | Required. Any valid expression. |
| format | Optional. A valid named or user-defined format expression. |
| firstdayofweek | Optional. A constant that specifies the first day of the week. |
| firstweekofyear | Optional. A constant that specifies the first week of the year. |
Settings
The firstdayofweek argument has these settings
| Constant | Value | Description |
|
| vbUseSystem | 0 | Use NLS API setting. |
| VbSunday | 1 | Sunday (default). |
| vbMonday | 2 | Monday. |
| vbTuesday | 3 | Tuesday. |
| vbWednesday | 4 | Wednesday. |
| vbThursday | 5 | Thursday. |
| vbFriday | 6 | Friday. |
| vbSaturday | 7 | Saturday. |
The firstweekofyear argument has these settings
| Constant | Value | Description |
|
| vbUseSystem | 0 | Use NLS API setting. |
| vbFirstJan1 | 1 | Start with week in which January 1 occurs (default). |
| vbFirstFourDays | 2 | Start with the first week that has at least four days in the year. |
| vbFirstFullWeek | 3 | Start with the first full week of the year. |
Remarks
| To Format | Do This |
|
| Numbers | Use predefined named numeric formats or create user-defined numeric formats. |
| Dates and times | Use predefined named date/time formats or create user-defined date/time formats. |
| Date and time serial numbers | Use date and time formats or numeric formats. |
| Strings | Create your own user-defined string formats. |
If you try to format a number without specifying format, Format provides functionality similar to the Str function, although it is internationally aware. However, positive numbers formatted as strings using Format don't include a leading space reserved for the sign of the value; those converted using Str retain the leading space.
See Also
Format function different formats for different numeric values, Format function different formats for different string values, Format function named date/time formats, Format function named numeric formats, Format function user-defined date/time formats, Format function user-defined numeric formats, Format function user-defined string formats, Str function.
Specifics (Microsoft Access)
In Microsoft Access versions 1.x and 2.0, you could use the Format function to return one value for a zero-length string and another for a Null value. For example, you could use a format expression such as the following with the Format function to return the appropriate string value from code:
Dim varX As Variant, varStrX As Variant
' Assign some value to varStrX and pass to Format function.
varX = Format(varStrX, "@;ZLS;Null")
In Microsoft Access 97, you must test separately for the Null case, then return the appropriate value based on the result. For example, you could use the IIf function in an expression with the Format function such as the following:
varX = IIf(IsNull(varStrX),"Null", Format(varStrX, "@;ZLS"))
This change applies only when you use the Format function to format a string dependent on whether it's a zero-length string or a Null value. Other format expressions used with the Format function continue to work as they did in previous versions.
If you convert a database from Microsoft Access version 1.x or 2.0 to Microsoft Access 97, you'll need to change code to test separately for the Null case.
Example
This example shows various uses of the Format function to format values using both named formats and user-defined formats. For the date separator (/), time separator (:), and AM/PM literal, the actual formatted output displayed by your system depends on the locale settings on which the code is running. When times and dates are displayed in the development environment, the short time format and short date format of the code locale are used. When displayed by running code, the short time format and short date format of the system locale are used, which may differ from the code locale. For this example, English/U.S. is assumed.
MyTime and MyDate are displayed in the development environment using current system short time setting and short date setting.
Dim MyTime, MyDate, MyStr
MyTime = #17:04:23#
MyDate = #January 27, 1993#
' Returns current system time in the system-defined long time format.
MyStr = Format(Time, "Long Time")
' Returns current system date in the system-defined long date format.
MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday,
' Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23) ' Returns "23".
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".