How to Format the Number Field for Microsoft Access Calculations

Communicate your numbers effectively with field formats.

Your Microsoft Access calculations will be more clear if they are formatted properly. Formats make your database more user-friendly and accessible by bridging the gap between computer and user. For example, a calculated field displayed as 19 is more meaningful when formatted as a currency: $19.00. Whether your calculated field is stored in a table, query, form or report, you can format it by selecting a built-in format or defining your own.

Instructions

1. Open the object that contains the number field you want to format. Tables, queries, forms and reports allow calculated fields to be formatted in a variety of ways. Select the "Home" tab of the ribbon at the top of the page, click the "View" button and select "Design View."

2. Select the calculated field of a table and click on the drop-down box next to the "Format" box in the field properties. Choose one of the format options or define your own by typing in a custom format. One example of a custom format is 0;(0);;"Null", which formats negative numbers and null values differently than positive numbers, increasing the readability of your data.

3. Open the "Property Sheet" to alter the format of a calculated number field in a query, form or report. Click the "Property Sheet" button on the ribbon at the top of the page and select the number field. Select the drop-down box on the "Format" menu to choose a built-in number format or define your own custom format.

Tips & Warnings

• When defining your own custom format, use the # symbol to hold the place of a number, the % symbol to convert your decimal number to a percentage and the letter E to place your calculation in scientific notation.

References

• Microsoft Access: Format Property - Number and Currency Data Types