Tables & Queries:
Format Fields

Step-by-Step: Formatting Fields

---

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:  , 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. Select  Tables and click 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. Select 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 to add it to the list on the right, the fields for your new table. The list in the center does not change.

   

   EmployeeID SocialSecurityNumber NationalEmplNumber FirstName MiddleName LastName Title EmailName Extension Address City StateOrProvince Region PostalCode Country/Region HomePhone BirthDate SpouseName EmrcyContactName EmrcyContactPhone Notes

    

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

8. 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. Select 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. Click on the button Relationships in the dialog.
     A new window appears with some choices.
     

15. Choose 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.
    The 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. Select to Modify the table design.
     

19. Click on Finish.
    
    Your new table opens in Design View.
     

---

Enter Data

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

1. 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
   Extension	ViajesMundiales.net
   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

   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.

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

   Before 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. 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 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. 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. Save the table.
    
6. 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. 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.
    
   The Format Property does not change what is stored in the field, only the way it is shown.

---

Using an Input Mask: Enter Value (Date)

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

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

1. Click 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.
    
   Problem: 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
    
   Problem: 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.
   
   Solution: Click on OK and then delete the date. Try again.

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

   

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

6. In 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. Press 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. In the Birthdate field type 021454.
   Finally! The date is the one we wanted.
    

9. Press 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!
   Point 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.

   

   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. Try 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. 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. Type 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.
    
   Caption: 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. Save the table,
    

5. Switch back to the Datasheet view.
    

6. 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!
   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.
   Problem: The second placeholder was selected instead.
          Solution: Press the left arrow key to move left one character.
   Problem: No @ symbols show.
         Solution: Click out of the cell to deselect it and try again.
    

7. 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. 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. Switch back to Table Design view.
    

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

3. Save the table.
    

4. 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. Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.