MS Access Format() Function
The Format() function in MS Access allows you to customize the way numbers and dates are displayed. You can specify a format to control how the output string appears, including things like decimal places, thousands separators, currency symbols, and date styles.
Format(): Definition and Usage
Format() is extremely useful for presenting data in a clear, consistent, and user-friendly way. It's particularly helpful when generating reports or displaying formatted values in forms or other interfaces.
Syntax
Syntax
Format(value, format)
Parameter Values
| Parameter | Description |
|---|---|
value |
The number or date you want to format. This is required. |
format |
A string specifying the desired format. This is optional; if omitted, a general number format is used. See the table below for format options. |
Format Options
| Format | Description |
|---|---|
| General Number | A number without thousand separators. |
| Currency | Currency format with thousand separators and two decimal places. |
| Fixed | At least one digit before the decimal, two after. |
| Standard | Thousand separators, at least one digit before decimal, two after. |
| Percent | Percent format with two decimal places and a percent sign. |
| Scientific | Scientific notation. |
| Yes/No | Displays "Yes" if non-zero, "No" if zero. |
| True/False | Displays "True" if non-zero, "False" if zero. |
| On/Off | Displays "On" if non-zero, "Off" if zero. |
| General Date | Date based on system settings. |
| Long Date | Long date format based on system settings. |
| Medium Date | Medium date format based on system settings. |
| Short Date | Short date format based on system settings. |
| Long Time | Long time format based on system settings. |
| Medium Time | Medium time format based on system settings. |
| Short Time | Short time format based on system settings. |
Example
Formatting a Number as Currency
This example 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
--------------
(Prices formatted as currency, with thousand separators and two decimal places based on regional settings)