MS Access Format() Function
The Format() function in MS Access lets you customize how numbers and dates are displayed. You can use it to control the appearance of your numerical and date/time data, making it more readable and user-friendly.
Format(): Definition and Usage
Format() is very helpful for presenting data in a clear and consistent way. It's commonly used in reports, forms, or any other situation where you need to control the precise format of numbers and dates. It takes the value to format and the format string as input.
Syntax
Syntax
Format(value, format)
Parameter Values
| Parameter | Description |
|---|---|
value |
The numeric or date value you want to format. This is required. |
format |
A string specifying the format. This is optional; if omitted, a general number format is used. See the table below for options. |
Format Options
| Format | Description |
|---|---|
| General Number | Displays a number without any formatting (no thousands separators). |
| Currency | Displays a number as currency, with thousands separators and two decimal places. |
| Fixed | Displays a number with at least one digit before the decimal point and two after. |
| Standard | Displays a number with thousands separators, at least one digit before the decimal point, and two after. |
| Percent | Displays a number as a percentage with a percent sign and two decimal places. |
| Scientific | Displays a number in scientific notation. |
| Yes/No | Displays "Yes" if the value is non-zero, "No" if zero. |
| True/False | Displays "True" if the value is non-zero, "False" if zero. |
| On/Off | Displays "On" if the value is non-zero, "Off" if zero. |
Examples
Formatting a Number as a Percentage
This formats the number 0.5 as a percentage.
Syntax
SELECT Format(0.5, "Percent") AS FormattedNum;
Output
50%
Formatting a Number as Currency
This formats the 'Price' column from the 'Products' table as currency. (Assumes a 'Products' table exists with a 'Price' column.)
Syntax
SELECT Format(Price, "Currency") AS FormattedPrice FROM Products;
Output
FormattedPrice
--------------
(The prices formatted as currency will be displayed here, based on your regional settings.)