|
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
| 
dzone
digg 
del.icio.us
stumbleupon