Formatting Access query values and editing column headings

Access Tables and Queries only contain basic presentation tools because it is assumed that data will be presented to users in Forms or Reports. Nevertheless it is possible to change the column headings and the formatting appearance of data in each column.

Access will assign data in a new query the default 'format' as specified in the field properties in the underlying table. If the original table field properties include a 'caption' value, this will be displayed as the column heading in the query, rather than the field name.

It may be that the default properties were either not set, or are not appropriate in the display of results in a particular query. They can be amended using the properties of a particular field. In the lower part of the query design view window, select a field and then open the properties dialog box (either via the Query Tools Design ribbon - Show Hide | Properties or the right click shortcut menu).

Choose whether to create new query fields with data values modified to a particular format, or to change the appearance of the field contents using built in formats or customised patterns.

Examples of changing the appearance of text:

... and to change the appearance of other values:

The formatting codes are mostly the same as in Excel and more information can be found here.

Note that one of the examples above uses the ConvStr() function. This can be used to transform text in one of three ways. =ConvStr(TargetString, TransformType) where:

1 - Converts the string to uppercase characters.
2 - Converts the string to lowercase characters.
3 - Converts the first letter of every word in string to uppercase.

The Caption property of a field can be used to set a column heading as something other than the actual field name.

Using the field properties and query design, unformatted values can be transformed from this:

To this: