Format Property - Text Data Types

You can use special characters in the setting for the Format property to create custom formats for text fields.

Setting

The following table summarizes the text formatting characters and how they work.

Character

Description

@

Used to display any available character for its position in the format string. If Access places all characters in the underlying data, any remaining placeholders appear as blank spaces.

For example, if the format string is @@@@@ and the underlying text is ABC, the text is left-aligned with two leading blank spaces.

&

Used to display any available character for its position in the format string. If Access places all characters in the underlying data, any remaining placeholders display nothing.

For example, if the format string is &&&&& and the text is ABC, only the left-aligned text is displayed.

!

Used to force placeholder characters to be filled from left to right instead of right to left. You must use this character at the start of any format string.

<

Used to force all text to lowercase. You must use this character at the beginning of a format string, but you can precede it with an exclamation point (!).

>

Used to force all text to uppercase. You must use this character at the beginning of a format string, but you can precede it with an exclamation point (!).

*

When used, the character immediately after the asterisk (*) becomes a fill character — a character used to fill blank spaces. Access normally displays text as left-aligned and fills any area to the right of the value with blank spaces. You can add fill characters anywhere in a format string. When you do so, Access fills any blank spaces with the specified character.

Blank space, + - $ ()

Used to insert blank spaces, math characters (+ -), financial symbols ($ ¥ £), and parentheses as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), surround them with double quotation marks — note that you can place these characters anywhere in the format string.

"Literal text"

Use double quotation marks to surround any text that you want displayed to users.

\

Used to force Access to display the character that immediately follows. This is the same as surrounding a character with double quotation marks.

[color]

Used to apply a color to all values in a section of your format. You must enclose the name in brackets and use one of these names: black, blue, cyan, green, magenta, red, yellow, or white.

Remarks

Custom formats for Text fields can have up to two sections. Each section contains the format specification for different data in a field.

Section

Description

First

Format for fields with text.

Second

Format for fields with zero-length strings and Null values.

For example, if you have a text boxcontrol in which you want the word "None" to appear when there is no string in the field, you could type the custom format @;"None" as the control's Format property setting. The @ symbol causes the text from the field to be displayed; the second section causes the word "None" to appear when there is a zero-length string or Null value in the field.

You can also use the Format function to return one value for a zero-length string and another for a Null value

Examples

The following table shows examples of custom text 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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×