
Home	Excel	Access
\| Feedback \| Conditional Formatting \|

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 formatting as specified in the field properties in the underlying table. If the field properties include a caption value, this will be displayed as the column heading in the query.

It may be that the default properties were either not set, or are not most appropriate in the display of 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 Edit menu, the toolbar button 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
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

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:

Would you like to learn more?

click link below to find out about this book

Access 2003 Professional Results

Bookings_Extract: Select query
	Bkg Ref	User Group	Km End	Inv Raised	Invoice £
	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:

Bookings_Extract: Select query
	Bkg Ref	User Group	Km End	Inv Raised	Invoice £
	2436	CAPED CRUSADERS CLUB	70,121	23-Mar-05	£69.75
	2437	CHELSFIELD GIRL GUIDES	70,246	23-Mar-05	£26.55
	2438	ST MARTINS CHURCH CHOIR	70,988	04-Apr-05	£40.90
	2439	HIGH FLYERS ATHLETICS	70,335	04-Apr-05	£23.25

file: acc-formatfields.htm

Page last modified Nov06