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