MeadInKent
| Feedback | Conditional Formatting | Things to do with Queries |

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:

 Orig Data Value Displayed Value Using functions to amend the data Using field properties
ABC DEF abc def NewField: =LCase([OrigField]) <
ghi jkl GHI JKL NewField: =UCase([OrigField]) >
ABC DEF Abc Def NewField: =StrConv([OrigField],3) not possible

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

 Field Value Result Using functions to amend the data Default names Customised patterns
12345.6 12,346 NewField: =Format([OrigField],"#,##0") Standard #,##0;(#,##0)
12.34 12.34 NewField: =Format([OrigField],"#,##0.00") Currency #,##0.00;-#,##0.00
1234 00001234 NewField: =Format([OrigField],"00000000") N/A 00000000
25/12/2006 12-Dec-06 NewField: =Format([OrigField],"dd-mmm-yy") Medium Date dd-mmm-yy
25/12/2006 Wed 12 Dec NewField: =Format([OrigField],"ddd dd mmm") N/A ddd dd mmm
25/12/2006 20061225 NewField: =Format([OrigField],"yyyymmdd") N/A yyyymmdd

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.

A selection of books about Access from Amazon

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:

2 Bookings_Extract: Select query ox
BKG_REF USERGRP_NAME KM_RETURN INVOICE_DATE INV_AMOUNT
2436 CAPED CRUSADERS CLUB 70121 23/03/2005 69.754
2437 CHELSFIELD GIRL GUIDES 70246 23/03/2005 26.55
2438 ST MARTINS CHURCH CHOIR 70988 04/04/2005 40.9
2439 High Flyers athletics 70335 04/04/2005 23.25
 

To this:

2 Bookings_Extract: Select query ox
Booking User Group Km End Inv Raised Invoice
002436 CAPED CRUSADERS CLUB 70,121 23-Mar-05 69.75
002437 CHELSFIELD GIRL GUIDES 70,246 23-Mar-05 26.55
002438 ST MARTINS CHURCH CHOIR 70,988 04-Apr-05 40.90
002439 HIGH FLYERS ATHLETICS 70,335 04-Apr-05 23.25
 


file: acc-formatfields.htm Page last modified Apr14 MeadInKent.co.uk 2014