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, click on 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).

It is possible to either create new fields with data values actually modified to the new format, or to change the field formats using built in formats or customised patterns.

Examples of changing the appearance of text:

 Field Value Result 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.

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

Access 2010 All in One for Dummies

 
UK more ...   USA more ...
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