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:
||Displays date based on your system settings|
||Displays date based on your system's long date setting|
||Displays date based on your system's medium date setting|
||Displays date based on your system's short date setting|
||Displays time based on your system's long time setting|
||Displays time based on your system's medium time setting|
||Displays time based on your system's short time setting|
||Displays a number without thousand separators.|
||Displays thousand separators as well as two decimal places.|
||Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.|
||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.|
||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.|
||Displays No if the number is 0. Displays Yes if the number is not 0.|
||Displays True if the number is 0. Displays False if the number is not 0.|
||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:
||Uses the NLS API setting|
||Sunday (default, if parameter is omitted)|
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:
||Uses the NLS API setting|
||The week that contains January 1.|
||The first week that has at least 4 days in the year.|
||The first full week of the year.|
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'
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.
You can also use the Format function in a query.
Example with Dates:
SELECT Format([BirthDate],'yyyy/mm/dd') AS Expr1
Example with Numbers:
SELECT Format([Salary],'General Number') AS Expr1