About display formats for fields or controls (MDB) - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

About display formats for fields or controls (MDB)

Applies to: Microsoft Office Access 2003

Note The information in this topic applies only to a Microsoft Access database (.mdb).

You can use the Format property to specify the data display format (format: Specifies how data is displayed and printed. An Access database provides standard formats for specific data types, as does an Access project for the equivalent SQL data types. You can also create custom formats.) for a field or a control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). For example, you can choose to have all dates formatted using the month/day/year format or some other format. You can choose from a list of predefined formats for fields with the AutoNumber, Number, Currency, Date/Time, and Yes/No data types (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).), or you can create your own custom format for any field data type other than OLE Object.

In most cases, you'll want to set the property for the field in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). That way, the setting is automatically applied to the field in query Design view and to controls on a form or report bound (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) to that field. In certain cases, however, you may want to set the property in query Design view, form Design view, or report Design view. For example, you may want to display a different format in a table datasheet than in a report. You could set the Format property for the field in query Design view and then base your report on the query. If you're using an unbound control (unbound control: A control that is not connected to a field in an underlying table, query, or SQL statement. An unbound control is often used to display informational text or decorative pictures.), you'll need to set the Format property in form Design view or report Design view.

If you want to create literal display characters that help you enter data in a predefined format, you may want to use an input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) instead of a format.

Show Choosing between a display format and an input mask

Microsoft Access provides two field properties that produce similar results: the Format property and the InputMask property.

Use the Format property to display data in a consistent format. For example, if you set the Format property for a Date/Time field to Medium Date format, all dates entered will display in this form: 12-Jan-1999. If a user of your database enters a date as 01/12/99 (or any other valid date format), Microsoft Access will convert the display to the Medium Date format when the record is saved.

The Format property affects only how a value is displayed, not how it is stored in the table. Also, a display format (format: Specifies how data is displayed and printed. An Access database provides standard formats for specific data types, as does an Access project for the equivalent SQL data types. You can also create custom formats.) isn't applied until the data entered is saved— nothing is displayed in the field to suggest or control the format in which data is entered. If you want data to display exactly as entered, don't set the Format property. If you need to control how data is entered, use an input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) in addition to, or instead of, a data display format. An input mask ensures that the data will fit in the format you define, and you can specify the kind of values that can be entered.

If you define both a display format and an input mask for a field, Microsoft Access uses the input mask when you are adding or editing data, and the Format setting determines how the data is displayed when the record is saved. When using both Format and InputMask properties, be careful that their results don't conflict.

Show International data display formats

Microsoft Access can display a wide range of international formats. To help ensure consistency among applications, Microsoft Access uses the regional settings in Microsoft Windows Control Panel for the predefined Number and Date/Time formats. If your data isn't displayed in the right format for your country/region, change the regional settings specified in Control Panel.

Changes made to the formats of regional settings in Windows Control Panel will be automatically reflected in your database for Currency fields if the Format property setting is blank. However, this only affects the format; no conversion of currency values will be made. Therefore, transferring data between computers with different regional settings can result in incorrect currency data.

For example, when using the Currency format on a field where the Format property setting is blank, a value of 5,47 kr on a computer set for Denmark converts to $5.47 on a computer set for the United States. To prevent such errors, define a custom format for the currency, such as #,## kr. The custom format overrides the regional settings specified in Windows Control Panel. Similar problems won't occur when transferring standard number, date, or time data between computers with different regional settings.