Short Text and Long Text fields (also called Memo) are perhaps the most commonly used Data Types. With text formatting you have two choices: keep the default formats or create a custom format. When you apply a format to a table field, that same format is automatically applied to any form or report control that you subsequently bind to that table field. Formatting only changes how the data is displayed and does not affect how the data is stored or how users enter data.
In this article
Overview of text formats
The Short Text and Long Text (also called Memo) data types do not have predefined formats. The Short Text data type has only custom formats. The Long Text data type has both custom and Rich Text formatting. If you don't specify a format, Access left aligns all text in datasheets.
Typically, you apply custom formats to Short Text and Long Text data types to make the table data easier to read. For example, if you use a form to collect credit card numbers, and you store those numbers without spaces, you can use a custom format to add the appropriate spaces to make the credit card numbers easier to read.
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 in the first section causes the text from the field to be displayed and the second section causes the word "None" to appear when there is a zero-length string or Null value in the field.
You can use the Format function to return one value for a zero-length string and another for a Null value
To control the format in which data is entered, see Control data entry formats with input masks. To enhance plain text in a Long Text Data Type, see Add a rich text field.
Apply a custom format
-
Open the table in Design View.
-
In the upper section of the design grid, select the Date/Time field you want to format.
-
In the Field Properties section, select the General tab, click the cell next to the Format box and enter the specific characters based on your formatting needs.
-
After you enter a format, the Property Update Options button appears and lets you apply the format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere <Field Name> is used. In this case, Field Name is the name of your Text field.
-
If you choose to apply your changes to the entire database, the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes to apply the format.
For more information, see Propagate a field property.
-
Save your changes, and then switch to Datasheet view to see whether the format meets your needs.
-
Test your custom text formatting in the following ways:
-
Enter uppercase or lowercase values, and see how the format treats the data.
-
Enter a zero-length string or a null value, and see if you like the result.
-
Examples of custom formats
The following are examples of custom text formats.
|
Setting |
Data |
Display |
|
@@@-@@-@@@@ |
465043799 |
465-04-3799 |
|
@@@@@@@@@ |
465-04-3799 |
465-04-3799 |
|
> |
davolio |
DAVOLIO |
|
< |
davolio |
davolio |
|
@;"Unknown" |
Null value |
Unknown |
|
Zero-length string |
Unknown |
|
|
Any text |
Same text as entered is displayed |
Custom format characters
Note: Applying any custom format to Long Text or Memo fields truncates the display to 255 characters.
To create a custom format, use the following characters as placeholders and separators.
|
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. |
