Microsoft access manual

FORMAT PROPERTY

You can use the following symbols in custom formats for any data type.

Symbol	Meaning
(space)	Display spaces as literal characters.
"ABC"	Display anything inside quotation marks as literal characters.
!	Force left alignment instead of right alignment.
*	Fill available space with the next character.
\	Display the next character as a literal character. You can also display literal characters by placing quotation marks around them.
[color]	Display the formatted data in the color specified between the brackets. Available colors: Black, Blue, Green, Cyan, Red, Magenta, Yellow, White.

- Text and Memo Data Types

Show All

Setting

You can create custom text and memo formats by using the following symbols.

Symbol	Description
@	Text character (either a character or a space) is required.
&	Text character is not required.
<	Force all characters to lowercase.
>	Force all characters to uppercase.

Custom formats for Text and Memo fields can have up to two sections.

Section	Description
First	Format for fields with text.
Second	Format for fields with zero-length strings (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").) and Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values.

Example

The following are examples of text and memo custom formats.

Setting	Data	Display
@@@-@@-@@@@	465043799	465-04-3799
@@@@@@@@@	465-04-3799 465043799	465-04-3799 465043799
>	davolio DAVOLIO Davolio	DAVOLIO DAVOLIO DAVOLIO
<	davolio DAVOLIO Davolio	davolio davolio davolio
@;"Unknown"	Null value	Unknown
	Zero-length string	Unknown
	Any text	Same text as entered is displayed

- Number and Currency Data Types

Custom Formats

Custom number formats can have one to four sections with semicolons (;) as the list separator. Each section contains the format specification for a different type of number.

Section	Description
First	The format for positive numbers.
Second	The format for negative numbers.
Third	The format for zero values.
Fourth	The format for Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values.

For example, you could use the following custom Currency format:

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

Symbol	Description
. (period)	Decimal separator. Separators are set in the regional settings in Windows.
, (comma)	Thousand separator.
0	Digit placeholder. Display a digit or 0.
#	Digit placeholder. Display a digit or nothing.
$	Display the literal character "$".
%	Percentage. The value is multiplied by 100 and a percent sign is appended.

Example

The following are examples of custom number formats.

Setting	Description
0;(0);;"Null"	Display positive values normally; display negative values in parentheses; display the word "Null" if the value is Null.
+0.0;–0.0;0.0	Display a plus (+) or minus (–) sign with positive or negative numbers; display 0.0 if the value is zero.

- Yes/No Data Type

Custom Formats

The Yes/No data type can use custom formats containing up to three sections.

Section	Description
First	This section has no effect on the Yes/No data type. However, a semicolon (;) is required as a placeholder.
Second	The text to display in place of Yes, True, or On values.
Third	The text to display in place of No, False, or Off values.

Example

The following example shows a custom yes/no format for a text box control. The control displays the word "Always" in blue text for Yes, True, or On, and the word "Never" in red text for No, False, or Off.

;"Always"[Blue];"Never"[Red]

- Date/Time Data Type

Custom Formats

You can create custom date and time formats by using the following symbols.

Symbol	Description
: (colon)	Time separator (separator: A character that separates units of text or numbers.). Separators are set in the regional settings of Windows.
/	Date separator.
c	Same as the General Date predefined format.
d	Day of the month in one or two numeric digits, as needed (1 to 31).
dd	Day of the month in two numeric digits (01 to 31).
ddd	First three letters of the weekday (Sun to Sat).
dddd	Full name of the weekday (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).
ww	Week of the year (1 to 53).
m	Month of the year in one or two numeric digits, as needed (1 to 12).
mm	Month of the year in two numeric 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 displayed as the quarter of the year (1 to 4).
y	Number of the day of the year (1 to 366).
yy	Last two digits of the year (01 to 99).
yyyy	Full year (0100 to 9999).
h	Hour in one or two digits, as needed (0 to 23).
hh	Hour in two digits (00 to 23).
n	Minute in one or two digits, as needed (0 to 59).
nn	Minute in two digits (00 to 59).
s	Second in one or two digits, as needed (0 to 59).
ss	Second in two digits (00 to 59).
ttttt	Same as the Long Time predefined format.
AM/PM	Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate.
am/pm	Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate.
A/P	Twelve-hour clock with the uppercase letter "A" or "P", as appropriate.
a/p	Twelve-hour clock with the lowercase letter "a" or "p", as appropriate.
AMPM	Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.

Example

The following are examples of custom date/time formats.

Setting	Display
ddd", "mmm d", "yyyy	Mon, Jun 2, 1997
mmmmdd", "yyyy	June 02, 1997
"This is week number "ww	This is week number 22
"Today is "dddd	Today is Tuesday

INPUTMASK PROPERTY

Character	Description
0	Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9	Digit or space (entry not required, plus and minus signs not allowed).
#	Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).
L	Letter (A to Z, entry required).
?	Letter (A to Z, entry optional).
A	Letter or digit (entry required).
a	Letter or digit (entry optional).
&	Any character or a space (entry required).
C	Any character or a space (entry optional).
. , : ; - /	Decimal placeholder and thousand, date, and time separators (separator: A character that separates units of text or numbers.). (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).
<	Causes all characters to be converted to lowercase.
>	Causes all characters to be converted to uppercase.
!	Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
\	Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Example

The following are examples of input mask.

Input mask	Sample values
(000) 000-0000	(206) 555-0248
(999) 999-9999	(206) 555-0248
	( ) 555-0248
(000) AAA-AAAA	(206) 555-TELE
#999	–20
	2000
>L????L?000L0	GREENGR339M3
	MAY R 452B7
>L0L 0L0	T2F 8M4
00000-9999	98115-
	98115-3007
>L	Maria
	Brendan
SSN 000-00-0000	SSN 555-55-5555
>LL00000-0000	DB51392-0493