Advertisements

Format Function

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

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.

See some examples

Syntax

Format( expression [, format ] [firstdayofweek ] [firstweekofyear ] )

The Format function syntax has these arguments:

ArgumentDescription
expressionRequired. Any valid expression.
formatOptional. A valid named or user-defined format expression.
firstdayofweekOptional. A constant that specifies the first day of the week.
firstweekofyearOptional. 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 typeFormat Property
Date/timeFormat a date and time field
NumericFormat a number or currency field
Text and memoFormat a text field
Yes/NoFormat Property – Yes/No Data Type

The firstdayofweek argument has these settings:

ConstantValueDescription
vbUseSystem0Use NLS API setting.
VbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

The firstweekofyear argument has these settings:

ConstantValueDescription
vbUseSystem0Use NLS API setting.
vbFirstJan11Start with week in which January 1 occurs (default).
vbFirstFourDays2Start with the first week that has at least four days in the year.
vbFirstFullWeek3Start with the first full week of the year.

Remarks

To FormatDo This
NumbersUse predefined named numeric formats or create user-defined numeric formats.
Dates and timesUse predefined named date/time formats or create user-defined date/time formats.
Date and time serial numbersUse date and time formats or numeric formats.
StringsCreate your own user-defined string formats.

If you try to format a number without specifying formatFormat 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.

SymbolRange
d1-30
dd1-30
ww1-51
mmmDisplays full month names (Hijri month names have no abbreviations).
y1-355
yyyy100-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".

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

See Also

String functions and how to use them

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Advertisements

Leave a Reply

%d bloggers like this: