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. |
