Tables & Queries:
Format Fields

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

How do you want to see the data in your fields? Do you want dates to show like 1/4/2006 or January 4, 2006? Do you want a phone number to look like 123-555-6789 or 1235556789? Does a text field need to look like abcd or ABCD?

You can make choices like these in the Table Design View. There are two properties that you can use to control how a field's values show, Format and Input Mask.

TipInherited: The Format and Input Mask properties also apply to the field when it is part of a query, form, or report. You can make a different choice in the Design View for the query, form, or report.

WarningUsing both: If you define both the Format and Input Mask properties for a field,  be sure that they do not conflict with each other! You will see the Input Mask only when you are entering or editing data. The Format is applied only after the record is saved.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing Tables Arrow: subtopic open
    Icon: StepDesign Tables
    Icon: StepFormat Fields
    Icon: StepIndexes
    Icon: StepValidation
    Icon: StepRedesign Table
    Icon: StepDocument a Table
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports


Format Property

Format: Controls how the value is displayed, but does not affect what is actually stored in the database.

The formatting is applied only after the value has been saved.

What are your choices? It depends on the field's type. Many types have a drop list in the Format property box.

Date/Time Table Design View: Field Properties - Date/Time type
Number or Currency Table Design View: Field Properties - Number type

The currency and decimal symbols used are set in Regional & Language Options in the Control Panel.

Yes/No Table Design View: Field Properties - Yes/No type
Text or Memo

There is no drop list for Text and Memo field types. You can create your own formats, using symbols. The Table Wizard creates some advanced formats automatically, which you could copy to other tables.

Examples of formats for Text or Memo fields

Input Mask Property

Input Mask: Controls how you enter or edit the data.

You can use certain symbols to restrict what kinds of characters are allowed and to show how many are allowed. You can save or not save literal characters like hyphens, spaces, parentheses, and periods as part of the value.

Input Masks are used mostly with Text and Date/Time fields, but can also be used in Number or Currency fields.

After the data is saved, the Format property controls what you see. Think carefully before setting both Format and Input Mask properties for the same field.

Parts of an Input Mask: An input mask has 3 sections, separated by a semi-colon (;)
   <the mask> ; <save literals? 0 or 1> ; <placeholder symbol>

Example:   !999(999)000-0000;0;_  for a phone number with country code and area code.

Part 1: Mask itself, like !999(999)000-0000 in the example above for a phone number.
Once you click in the field itself, you would see:  (___)___-____

  1. !  = Accept typing starting from the right instead of from the left

  2. 0 = You must enter a digit for each 0 in the mask.

  3. 9  = You can enter a digit in where you see a 9 in the mask, but you do not have to.

Part 2: Save literal characters in the mask? (like the parentheses and hyphen in the example)

  1. 0  = Yes, store characters with the data.

  2. 1  =  No, do NOT store characters with the data.

Part 3: Placeholder: The character to use to show how many characters to enter.
The example above uses an underscore. You can use any character as a placeholder. To not show a placeholder at all, use "" in the third position.

Field Properties: Input Mask - button with 3 dots opens a wizardInput Mask Wizard:  The wizard offers masks for common fields like US phone numbers, postal codes, social security numbers. You open this wizard from the button with three dots Button: 3 dots, which appears when you select the Input Mask box for the field.

Dialog: Input Mask WizardIn the wizard's Try It box you can enter an example value to see if this mask will work well for the values you expect. To use a mask, you must know what to expect!!

Dialog: Customize Input Mask WizardCustomize the list: The Edit List button opens another dialog that shows the definition of the selected mask. You can enter a new Description and change the mask. Clicking the Close button adds it to the list.

Symbols for custom Input Masks

Icon: Step-by-Step 

Step-by-Step: Formatting Fields

 Icon: Step-by-Step

What you will learn:

to create a table with Table Wizard
to set the Format property for a field
to enter data using an input mask
to edit data in a field with an Input Mask
to create a custom Input Mask for a field

Start with:  Class disk, Projects database open.

The story so far: 
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects. You designed two tables in the last lesson but now see that you need a Staff table also.

You will create a Staff table using the Table Wizard and then look at Format and Input Mask properties.

Create Staff Table with Table Wizard

  1. If necessary, switch to the Database Window in your Projects database.

  2. Dialog: New Table - Table Wizard selectedSelect  Tables and click Button: New table the New button. The New Table dialog appears.

  3. Click on Table Wizard and then on OK.
    The Table Wizard dialog appears with a number of pre-designed tables for common situations.

  4. Dialog: Table Wizard - Busines, EmployeesSelect Business, if necessary.

  5. Select Employees, from the Sample tables list. A list of fields from this sample table appears in the center.
    You do not need all of these fields. You can rename them or add others after creating the initial table.

  6. Using the list below, click a field in the center list of the Table Wizard. Then click the > button Button: > (move selected item to the list at the right) to add it to the list on the right, the fields for your new table. The list in the center does not change.

    Dialog: Table Wizard - selecting fields






















  7. Click on Next.
    The next step in the wizard asks you about the table's name and primary key .

  8. Dialog: Table Wizard - name table - use primary key?Change the table's name to Staff.

  9. Choose to set the primary key yourself.

  10. Click on Next.
    The next step in the wizard sets the primary key.

  11. Dialog: Table Wizard - select field as primary keySelect the field EmployeeID as your primary.

  12. Choose to use consecutive numbers that MS Access automatically assigns as each record is created.

  13. Click on Next.
    The next step tries to identify what tables the new one might be related to.
    Access cannot see any relationships. You will be making changes, however that will make the new Staff table related to the ProjectStaff table.

  14. Dialog: Table Wizard - related to what other tables?Click on the button Relationships in the dialog.
     A new window appears with some choices.

  15. Dialog: Table Wizard - RelationshipsChoose the middle item, One record in the Staff table will match many records in the ProjectStaff table.

  16. Click on OK to close the Relationships dialog.
    Dialog: Table Wizard: related to ProjectStaffThe Table Wizard window now shows that Staff is related to ProjectStaff .

  17. Click on Next.
    The next step asks if you are ready to enter data or do you want to modify the design.

  18. Dialog: Table Wizard - finishingSelect to Modify the table design.

  19. Click on Finish.

    Table Design View: StaffYour new table opens in Design View.

Enter Data

To see how field properties work, you first need a record!

  1. Icon: Datasheet Switch to Datasheet View for the table Staff.

  2. Enter the following as the first record, exactly as shown:
    (Leave the other fields blank for now.)

    First Name Luis
    Middle Name P
    Last Name Perez
    Title President
    Email Name LuisPerez
    Address Montevideo 6553
    City Buenos Aires
    Postal Code 1123
    Country/Region Argentina
    Home Phone +54 (1) 555-1234
    Spouse Name Rosa Perez
    Emrgcy Contact Name Rosa Perez
    Emrgcy Contact Phone +54 (1) 555-1234

    Warning Beware of Undo! The Undo command will wipe out the whole record if it has not been saved! After saving, Undo removes all of the changes that you made since saving the record.

    TipTo save a record:

    • Just change to a different record.

    • Menu: select Records | Save Record.

    • Keyboard use the key combo SHIFT + ENTER.

    • Toolbar: click the Save button Button: Save .

    TipBefore leaving a field, pressing the ESC key will return a field to its previous value.

    Note: The person's name is in three fields. The email address is in 2 fields.

    Note: The phone numbers in Argentina have a different form than in the USA. It's a good thing that the wizard did not apply a Format or Input Mask property for the phone numbers. Depending on the countries that you need to work with, you might want to split each phone number up into parts, like with the email address and name fields.

  3. Drag right edge of header to the right to make the column wider.Widen columns: Drag the right edge of the heading to the right for any fields that are too narrow to show all of the characters.
  4. Click the Save button Button: Save to save the record.

Format Property: < (forcing lower case)

Since this table will have records involving several countries, you cannot easily apply formatting to postal codes or phone numbers or even addresses. You can use formatting to make sure that the email address parts are all lower case.

  1. Design view: Email name - Format as lower caseIcon: Design Switch to Design View for the table Staff.
  2. Select the field EmailName.
  3. In the Format box, type < .
  4. Repeat for the field Extension.
  5. Icon: Save Save the table.
  6. Datasheet: Email name: formatted as lower caseIcon: Datasheet Switch back to Datasheet View.
    Both of the fields are now in all lower case letters.
    (Did you widen the Extension field to see all of the characters?)
  7. Icon: Experiment Experiment by typing various combinations of lower case and upper case letters in these fields and then changing to a different field. The value is shown in lower case only after you leave the field or save the record.
    TipThe Format Property does not change what is stored in the field, only the way it is shown.

Using an Input Mask: Enter Value (Date)

Table Design: Birthdate fieldThe Table Wizard set both the Format and Input Mask Properties for the field, Birthdate. Sometimes these can conflict with each other, or at least be confusing.
Now you will enter a date into the Birthdate field to see how an input mask works. After you save the date, the Format property displays the date in Short Date format.

Warning Remember: Using the Undo command will wipe out the whole record if it has not been saved!

  1. Table Datasheet: Birthdate - inpout maskClick in the Birthdate field.
    A mask appears - blanks and slash characters. The cursor is a thick black bar, showing where the next character will go.
    Icon: TroubleProblem: Mask does not appear: Did you click in the field or did you use TAB or arrow keys to get into the field? Sometimes moving with the arrow keys or TAB key does not make the mask appear right away.
    Solution: Start typing anyway. Once you start typing the mask appears.

  2. In the Birthdate field type February 14, 1954.
    Does your typing show at all?? The letters do not! Letters are not allowed by the input mask:    99/99/00;0

    Explanation of the mask 99/99/00;0
    Where you see 9 in the mask, you can type a digit or a space.

    Where you see a 0, you must type a digit, and not a space.

    BUT, if you want to type just one digit for a month or day, it must be in place of the second 9, not the first one.

    So... you can type: space 5 space 4 6 4 and Access will show the date 5/4/64. But if you type 5 space 4 space 6 4, then Access will show an error message and refuse your date!

     Details on custom Input Masks
    Birthdate wrong - February, 14, 1954Icon: TroubleProblem: February 14, 1954 does show in the field
    If you delete a value from a field, you may be able to type values that do not match the input mask... but the value will not be accepted.

    If you save the value by moving to a different field or saving the record, then a message appears that says that the value is not appropriate for the input mask.
    Message: The value you entered is not appropriate for the input mask...
    Solution: Click on OK and then delete the date. Try again.

  3. Birthdate wrong - 21/41/95Click in the Birthdate field and type 2141954.
    Before you leave the field to save this value, the mask displays it as 21/14/95 and ignores the final 4 altogether! What is the 21st month, anyway!?

  4. Press the TAB key to move out of the field and save the value.
    A message appears that says your value is not valid. Your date is not a real date and Access recognized that when it tried to save it.

    Message: The value you entered isn't valid for this field.

  5. Select the date and press the Delete key to erase the value.

  6. Birthdate wrong - 2_/14/19In the Birthdate field type 2/14/1954, including the slashes, starting from the left. Watch the mask as you type!
    Whoops. These numbers do not fit the mask either! Typing the slash moved the cursor to the next section of the mask, but only the first two digits of the year fit in the mask.

  7. Birthdate wrong - 2/14/2019Press the TAB key to move to the next cell and save the value.
    The date is rewritten! Access did not remember the 54 and thought you meant 2019 for the year. Not good!! Even though this one is a legitimate date, its the wrong date.
    Let's try again.
    The mask requires 2 digits for the month and 2 for the day and 2 for the year.

  8. Birthdate correct - 02/14/54In the Birthdate field type 021454.
    Finally! The date is the one we wanted.

  9. Birthday formatted as 2/14/54 instead of 02/14/54Press the right arrow key to move to the next field.
    The field is reformatted, without the leading zero, in Short Date format. This is the Format Property at work!
    Icon: ConfusionPoint of Confusion: In this case the difference is subtle, but it causes a big confusion if you need to edit the date.

Using an Input Mask: Edit Value (Date)

When the Format Property formats a value differently from the input mask, trying to make changes can be frustrating! You must enter your values as the mask requires. If the mask does not show, you may have a hard time figuring out what is wrong.

  1. Drag across the 2 in the Birthdate to select it.

  2. Type a 3 and then click in another field to save the change.
    A message appears that says your value is not appropriate for the input mask.

    Message: The value you entered is not appropriate for the input mask...

    Even though the date is being displayed as 2/14/54, to edit it, you must match the mask, not what you see!!  Confusing!!!

  3. Birthdate corrected - 3/14/54Try again, replacing the 2 with 03 AND replace 1954 with 54.
  4. TAB or arrow or click on another field to save the change.
    The date is changed to 3/14/54.
    This is all VERY annoying... and crazy-making if you do not know what is going on!

Create a Custom Input Mask

Access provides a wizard for creating some common input masks plus you can create a custom mask using symbols. Your mask can demand a certain number of characters or allow for fewer. Your mask can require only numbers or only letters letters or it can allow any kind of character.

Now you will see the effect of an input mask when the data does not match.

Input Mask Vs. Existing Data: Creating an input mask does not cause an error if data that is already in the table does not match the mask. Only when you enter or edit data will an error occur.

  1. Icon: Design Switch to the Design view of the Staff table.

  2. Click on the field StateOrProvince and then in the box for the property Input Mask.

  3. Design view: Field StateOrProvince with mask and captionType AA;;@ in the box as the mask definition.
    The A means that you are required to enter a letter in this position, instead of a number or another character. There are no literal characters in this mask, so the second section of the definition is blank. The @ symbol will be the placeholder. This is easier to see than an underscore, but more confusing to a new user.
    TipCaption: The value in the Caption property will be the column heading (State/Province) instead of the name of the field (StateOrProvince). With a caption you can use characters that are not allowed in the name of a field, like the slash /character.

  4. Icon: Save Save the table,

  5. Icon: Datasheet Switch back to the Datasheet view.

  6. Field State/Province with input mask @@In the first record, click in the left side of the field State/Province, but not on the divider line. The first placeholder @ is selected.
    Is it obvious to you that you are to type two characters only? Probably.
    Is it obvious that they must be letters and not numbers or other characters? Probably not!
    Icon: Trouble Problem: Whole cell was selected instead.
           What happened:
    You caught the border of the cell when you clicked.
           Solution: Click out of the cell to deselect it and try again.
    Icon: Trouble Problem: The second placeholder was selected instead.
    Press the left arrow key to move left one character.
    Icon: Trouble Problem: No @ symbols show.
    Click out of the cell to deselect it and try again.

  7. Field State/Province- typed "Tenn"Type Tenn, which is a common abbreviation for the state of Tennessee, USA.
    Only the first two characters were kept. No error message!

  8. Delete the "Te"

  9. Field State/Province containing "tn"Type tn , which is the actual postal abbreviation for Tennessee, and press TAB to exit the cell.
    The two letters remain in lower case even though the mask used a capital A. That capital A does not tell the mask anything about the capitalization of what you type. It means that a letter or digit is required in this position. A lower case a would mean that a letter or digit is acceptable but is not required.
    (Yes, I know that the address in this record is in Argentina. We are experimenting here!)

Add Format

  1. Icon: Design Switch back to Table Design view.

  2. In the Format box for the field StateOrProvince, type > to force upper case for this field.

  3. Field State/Province formatted as TNIcon: Save Save the table.

  4. Icon: Datasheet Switch back to Datasheet view.
    The text is now in upper case, all capital letters, like the postal service wants.
    Of course since there is no state TN in Argentina, we have a little problem to deal with! Unhappily, there is no neat way to use an input mask for this field if you will have addresses from different countries. The same issue keeps us from using an input mask for telephone numbers and postal codes also. The joys of international business! <sigh>

  5. Icon: Design Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.