Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
|
Warning There is an issue with the use of this function. The last Monday in some calendar years can be returned as week 53 when it should be week 1. For more information and a workaround, see Format or DatePart functions can return wrong week number for last Monday in Year. |
Syntax
Format( expression [, format ] [, firstdayofweek ] [, firstweekofyear ] )
The Format function syntax has these arguments:
|
Argument |
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 format argument can use a variety of settings, depending on the data type of the expression argument. Consult the articles listed in the following table for more information about valid format expressions.
|
For expressions that use this data type... |
See the article |
|
Any type |
|
|
Date/time |
|
|
Numeric |
|
|
Text and memo |
|
|
Yes/No |
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.
If you are formatting a non-localized numeric string, you should use a user-defined numeric format to ensure that you get the look you want.
Note: If the Calendar property setting is Gregorian and format specifies date formatting, the supplied expression must be Gregorian. If the Visual Basic Calendar property setting is Hijri, the supplied expression must be Hijri.
If the calendar is Gregorian, the meaning of format expression symbols is unchanged. If the calendar is Hijri, all date format symbols (for example, dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar. Format symbols remain in English; symbols that result in text display (for example, AM and PM) display the string (English or Arabic) associated with that symbol. The range of certain symbols changes when the calendar is Hijri.
|
Symbol |
Range |
|
d |
1-30 |
|
dd |
1-30 |
|
ww |
1-51 |
|
mmm |
Displays full month names (Hijri month names have no abbreviations). |
|
y |
1-355 |
|
yyyy |
100-9666 |
Examples
Use the Format function in an expression You can use Format wherever you can use expressions. For example, you can use it in a query as part of a field alias, or in the Control Source property of a text box on a form or a report. The following examples shows an expression you might use in a report's Filter property to limit the output to records from the previous week.
Format([Date],"ww")=Format(Now(),"ww")-1
In this example, the report's record source has a field named Date, which contains the date each particular record was modified, and which is used on the report. When you run the report, its results are filtered to show only those records where the week for the value in the Date field (Format([Date],"ww")) is equal to the previous week (Format(Now(),"ww")-1).
Use the Format function in VBA code
Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.
This example shows various uses of the Format function to format values by 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 of the computer 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, the U.S. English locale is assumed.
MyTime and MyDate are displayed in the development environment using current system short time setting and short date setting.
Dim MyTime, MyDate, MyStrMyTime = #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".
Using Format with zero-length strings
In Microsoft Access version 2.0 and earlier, you could use the Format function to return one value for a zero-length string and another for a 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 VariantDim varStrX As Variant' Assign some value to varStrX and pass to Format function.varX = Format(varStrX, "@;ZLS;Null")
In Microsoft Access versions 97 and later, 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 2.0 and earlier to Microsoft Access 2002 or later, you must change code to test separately for the Null case.
