Access: Format Function

In Access, the Format function returns a string containing an expression formatted according to instructions contained in a format expression.

The syntax for the Format function is:

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

expression is the value to format. It can be any valid expression.

format is optional. It is a valid named or user-defined format expression. You can either define your own format or use one of the predefined Access formats such as:

Format Explanation
General Date Displays date based on your system settings
Long Date Displays date based on your system’s long date setting
Medium Date Displays date based on your system’s medium date setting
Short Date Displays date based on your system’s short date setting
Long Time Displays time based on your system’s long time setting
Medium Time Displays time based on your system’s medium time setting
Short Time Displays time based on your system’s short time setting
General Number Displays a number without thousand separators.
Currency Displays thousand separators as well as two decimal places.
Fixed Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
Standard Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.
Percent Displays a percent value – that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place.
Scientific Scientific notation.
Yes/No Displays No if the number is 0. Displays Yes if the number is not 0.
True/False Displays True if the number is 0. Displays False if the number is not 0.
On/Off Displays Off if the number is 0. Displays On is the number is not 0.

firstdayofweek is optional. It is a constant that specifies the first day of the week. If not specified, Sunday is assumed. This parameter can be one of the following values:

Constant Value Explanation
vbUseSystem 0 Uses the NLS API setting
VbSunday 1 Sunday (default, if parameter is omitted)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

firstweekofyear is optional. It is a value that specifies the first week of the year. If not specified, the first week is assumed to be the week in which Jan 1 occurs. This parameter can be one of the following values:

Constant Value Explanation
vbUseSystem 0 Uses the NLS API setting
vbFirstJan1 1 The week that contains January 1.
vbFirstFourDays 2 The first week that has at least 4 days in the year.
vbFirstFullWeek 3 The first full week of the year.

Example

Format (#25/05/2006#, "Long Date")      returns 'May 25, 2004'
Format (#25/05/2006#, "mm/dd/yyyy")     returns '05/25/2006'
Format ('0.745','Percent')              returns '74.50%'
Format ('25748','Currency')             returns '$25,748.00'

VBA Code

Dim MyResult, MyDate
MyDate =  #January 25, 2005#
MyResult = Format (MyDate, “yyyy/mm/dd”)

This example uses the Format function to format user-defined format. Now the MyResult variable would contain the date formatted as yyyy/mm/dd.

SQL query

You can also use the Format function in a query.

Example with Dates:

SELECT Format([BirthDate],'yyyy/mm/dd') AS Expr1
FROM EmployeeAddressTable 

Example with Numbers:

SELECT Format([Salary],'General Number') AS Expr1
FROM EmployeeStatisticsTable 
admin

admin

Leave a Reply

Your email address will not be published.