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
|