Customizing formats


Objective.

 

Customize the way data are displayed and printed.

Formats.

 

When we define a format for a specific field, we are telling Access how it should present the data stored in the field.

To simplify the task Access has predefined formats for severals data types, for example, for numerical fields Access has the standard number format, currency, etc..., for the date type we have the formats; short date, long date, time, etc...

But we can define our own formats. To define a custom format we can use any of the symbols below:

Symbol

Meaning

(space)

Show spaces as literal characters.

"ABC"

Show all the content inside quotation marks as literal characters.

!

Perform alignment to the left instead of to the right.

*

Fill the available space with the next character.

\

Show the next character as a literal character.

[colour]

Show the data in the colour specified between the square brackets. You can use: Black, Blue, Green, Cyan, Red, Magenta, Yellow, White.

Other than these symbols, we have other symbols assigned depending on the data type, as we will see next. It is not possible to mix the custom format symbols for Number and Currency with those format symbols of Date/Time, Yes/No, or Text and Memo.

We will now itemize the custom formats according to the type of data.

For Number and Currency fields.

The number formats can contain between one and four sections separated with semicolons (;). Every section contains the format especification for a different number.

Section

Description

First

Contains the format for positive numbers.

Second

Contains the format for negative numbers.

Third

Contains the format for zero value.

Fourth

Contains the format for null value.

 

For example, one can use the following custom format:

$#.##[Green];(0.00$)[Red];"Zero";"Null"

This format contains four sections separated by semicolons, and uses a different format for each section.

With this format we are indicating that the positive numbers (1st section) appear in a green colour, begin with a $ symbol, it does not show the insignificant zeros and only shows 2 decimals (#.##); the negative numbers are written in red, between brackets with a $ after and with always two decimals; the zero values are substituted with the word Zero and the null values with the word Null.

If youdo not specify a format for some section, the entries that have no format will not show anything or take the format of the first section.

You can create custom numeric formats using the following symbols:

Symbol

Description

, (comma)

decimal separater

. (period)

thousand separater.

0

Marks the position of digits. Shows a digit or 0.

#

Marks the position of the digits. Shows a digit or nothing.

$

Shows the $ character.

%

Percentage. The value is multiplied by 100 and a percentage symbol is attached.

E- or e-

Scientific notation with a minus (-) sign together with the negative exponents and nothing together with the positive exponents. This symbol must be used with other symbols,for example 00,0E-00 or 0.00E00.

E+ or e+

Scientific notation with a minus (-) sign together with the negative exponents and a plus (+) sign together with the positive exponents. This symbol should be used together with other symbols,for example 0,00E+00.

 

You can create custom date and time formats using these symbols.

Symbol

Description

: (colon)

time separator.

/

dates separator.

c

same as the General Date predefined format.

d

Day of the month in one or two digits, whichever is necessary ( 1 to 31).

dd

Day of the month in two numerical digits (01 to 31).

ddd

The first three letters of the day of the week (Sun to Sat).

dddd

Full name of the day of the week (Sunday to Saturday).

ddddd

same as the Short Date predefined format.

dddddd

same as the Long Date predefined format.

w

Day of the week (1 to 7).

www

Week of the year (1 to 53).

m

The month of the year in one or two numerical digits, whichever necessary ( 1 to 12).

mm

Month of the year in two numerical digits (01 to 12).

mmm

First three letters of the month (Jan to Dec).

mmmm

Full name of the month (January to December).

q

Date shown as a quarter of the year (1 to 4).

y

Number of the day of the year (1 - 366).

yy

Last two digits of the year (01 -99).

yyyy

Complete year (0100 to 9999).

h

Hour in one or two digits, whichever necessary (0 to 23).

hh

Hour in two digits (00 to 23).

n

Minutes in one or two digits, whichever necessary (0 to 59).

nn

Minutes in two digits (00 to 59).

s

Seconds in one or two digits, whichever necessary (0 to 59).

ss

Seconds in two digits (00 to 59).

ttttt

same as the Long Hour predefined format.

AM/PM

12 Hour clock with the uppercase letters AM or PM, whichever appropriate.

am/pm

12 Hour clock with the lowercase letters am or pm, whichever appropriate.

A/P

12 Hour clock with uppercase letters A or P, whichever appropriate.

a/p

12 Hour clock with the lowercase letters a or p, whichever appropriate.

AMPM

12 Hour watch with the appropriate morning/afternoon indicater.

 

The Yes/No data can use custom formats that contain up to three sections.

Section

Description

First

This section does not have any effect on the Yes/No. However, it requires a semicolon (;) symbol as a positional marker.

Second

The text to show in place of Yes, True or On value.

Third

The text to show in place of No, False or Off values.

   

 

For Text and Memo type fields.

One can create custom Texto and Memo formats using the following symbols.

Símbolo

Descripción

@

It needs a text character (whether it be a character or a space).

&

Does not need a text character.

<

Converts all the characters to lowercase.

>

Converts all the characters to uppercase.

The custom formats for Text and Memo fields can have two sections.

Each section contains the format specification for the various types of data of a field.

Sección

Descripción

First

Format for fields with text.

Second

Format for those fields that contain no character or null values.



   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.