Combo Box Control, Query Control, Table Fields, Text Box Control
You can use the Format property to customize the way numbers, dates, times, and text are displayed and printed. For example, if you’ve created a Price text box control, you can set its Format property to Currency. If you enter 4321.678 in the control, the number would be displayed as $4,321.68.
The Format property uses different settings for different data types. For information about settings for a specific data type, see one of the following topics.
For a control, you can set this property in the control’s property sheet. For a field, you can set this property in table Design view (under Field Properties) or in Design view of the Query window (in the Field Properties property sheet). You can also use a macro or Visual Basic.
You can use one of the predefined formats or you can create a custom format using formatting symbols.
Note In Visual Basic, enter a string expression that corresponds to one of the predefined formats or enter a custom format.
The Format property affects only how data is displayed. It does not affect how data is stored.
Microsoft Access provides predefined formats for Number, Date/Time, Yes/No, and Text and Memo data types. The predefined formats depend on the country specified in the Regional Settings section of the Windows Control Panel. Microsoft Access displays formats appropriate for the country selected. For example, with the United States indicated in the Regional Settings tab, 1234.56 in the Currency format appears as $1,234.56; but when the United Kingdom is indicated in the Regional Settings tab, the number appears as £1,234.56.
If you set a field’s Format property in table Design view, Microsoft Access uses that format to display data in datasheets. It also applies the field’s Format property to new controls on forms and reports.
You can use the following symbols in custom formats for any data type.
|
Symbol |
Meaning |
|
(space) |
Display spaces as literal characters. |
|
“ABC” |
Display anything inside quotation marks as literal characters. |
|
! |
Force left alignment instead of right alignment. |
|
* |
Fill available space with the next character. |
|
\ |
Display the next character as a literal character. You can place quotation marks around literal characters. |
|
[color] |
Display in the color named between the brackets. Available colors: Black, Blue, Green, Cyan, Red, Magenta, Yellow, White. |
You can’t mix custom formatting symbols for Number data types with Date/Time or with Text formatting symbols.
When you have defined an input mask and set the Format property for the same data, the Format property takes precedence when the data is displayed and the input mask is ignored. For example, if you create a Password input mask in table Design view and also enter the Format property, either in the table or in a control on a form, the Password input mask is ignored and the data is displayed according to the Format property.
DecimalPlaces Property, FieldSize Property, Format Function, InputMask Property.
The following three examples set the Format property using a predefined format.
Me![Date].Format = "Medium Date"![Time].Format = "Long Time"![Registered].Format = "Yes/No"
The next example sets the Format property using a custom format. This format displays a date as: Jan 1995.
Forms![Employees]![HireDate].Format = "mmm yyyy"
This example demonstrates a Visual Basic function that formats numeric data with the Currency format and formats text data entirely in capital letters. The function is called from the OnLostFocus event of an unbound control named TaxRefund.
Function FormatValue () As Integer
Dim varEnteredValue As Variant
varEnteredValue = Forms!Survey!TaxRefund.Value
If IsNumeric(varEnteredValue) = True Then
Forms!Survey!TaxRefund.Format = "Currency"
Else
Forms!Survey!TaxRefund.Format = ">"
End IfFunction