Format Function

Returns a Variant (String) containing an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) formatted according to instructions contained in a format expression.

See some examples

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 (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not 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 (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) 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.

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, 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".

      

ShowUsing 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 (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").) 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 Variant
Dim 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.

 
 
Applies to:
Access 2007