Jan's Working with Databases

Step-by-Step: Formatting Fields

---

What you will learn:

to create a table to create a relationship between tables to enter data to set the Format property for a field - lower case to create an input mask - date to edit data in a field with an Input Mask to create a custom Input Mask for a field to add Format to field definition

---

Start with:  , Projects database from previous lesson 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 and then look at Format and Input Mask properties.

Create Staff Table

In previous versions of Access the Table Wizard created fields for phone numbers and zip codes with input masks. But in Access 2007, 2010, 2013, and 2016, the table templates do NOT include input masks. So we will create the new table manually.

1. Back up the database now so you will have a good copy to use if you need to start over. This works well while the database is small.
   
   

   Access 2007: > Manage > Back Up Database

   Access 2010: File > Save and Publish > Back Up Database > Save As button

   Access 2013, 2016: File > Save As > Back Up Database > Save As button

2.  On the Create ribbon tab, click the Table Design button .
   A blank table opens in Table Design View.
3. Create the fields in the list below:
   

   Field Name	Data Type		Field Name	Data Type
   EmployeeID	AutoNumber, set as primary key		PostalCode	Text or Short Text
   SocialSecurityNumber	Text or Short Text		Country/Region	Text or Short Text
   NationalEmplNumber	Text or Short Text		HomePhone	Text or Short Text
   FirstName	Text or Short Text		WorkPhone	Text or Short Text
   MiddleName	Text or Short Text		WorkExtension	Text or Short Text
   LastName	Text or Short Text		MobilePhone	Text or Short Text
   Title	Text or Short Text		BirthDate	Date/Time
   EmailAddress	Text or Short Text		SpouseName	Text or Short Text
   Address	Text or Short Text		EmrcyContactName	Text or Short Text
   City	Text or Short Text		EmrcyContactPhone	Text or Short Text
   StateOrProvince	Text or Short Text		Notes	Memo or Long Text
   Region	Text or Short Text

   We usually use the Text or Short Text data type for values that look like numbers but which cannot be used in arithmetic, like a Social Security number or a phone number. Remember that text and numbers have different default alignment in a datasheet, form, or report.

4. Save the table with the name Staff.

---

Create Relationship

1. 

   Click on the button Relationships in the Database Tools ribbon tab.
   The Relationships window opens, showing the relationship that was already created between Project Staff and Projects.
    

2. Click the button Show Table .
   A dialog appears that lists all of the tables.
3. Click on the table Staff and then on the Add button.
   The table appears in the Relationships window.
   

4. Click on Close to close the dialog.
   The Relationships window shows three tables but only one join line.

   We need to join the tables Project Staff and Staff using the EmployeeID field. But Project Staff does not have a matching field. You will have to add one.

5. Open the Project Staff table in Design View.
6. Add a field EmployeeID as a Number data type.

7. Save the table.
   [Project Staff]

8. Switch back to the Relationships window.
   The list of fields for the Project Staff table was updated! Sweet!
9. Drag the EmployeeID field from the table Staff and drop on the field EmployeeID in the table
   Project Staff.
   
   When you drop, the Edit Relationship dialog opens.
   
10. Click on the button Create.
    The dialog closes and the Relationships window has a new join line.
    
11. Drag the tables in the Relationships window to put the Project Staff table in between the other two tables.
    The join lines move around, too.

---

Enter Data

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

1. Open the table Staff in Datasheet View.
   

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
   EmailAddress	LuisPerez@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.

   • Home ribbon tab > Save Record

   • Key combo: SHIFT + ENTER.

   • Quick Access Toolbar: Save button

   Get rid of typing in current field: Before leaving a field, press the ESC key to return a field to its previous value.

   Divided data: The person's name is in three fields. Other information is often divided up into separate fields, like an address which is split into fields for street address, city, state/province, country, postal code. Another example, an email address can be split into the name part and the extension.

   Where in the world is your data? It makes a difference!
   For example, the phone numbers in Argentina have a different form than in the USA, like +54 (1) 555-1234 instead of 1-123-456-7890. Street addresses in some place have the street number after the street name. In the USA the number is almost always before the street name. Some countries have postal codes that contain letters and have more or fewer characters than the USA's zip codes which have 5 numbers or 9. States in the USA have a two letter postal abbreviation, but other countries use more letters or do not abbreviate at all. You truly must know ahead of time whether you need to allow for other ways of doing things or not.

3. Widen columns: Double click the right edge of the heading for any fields that are too narrow to show all of the characters.
   The column widens to show the widest item showing in the datasheet in that column.
   
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 is all in lower case.

1. Switch to Design View for the table Staff.
   
2. Select the field EmailAddress.
   
3. In the Format box, type < .
   
4. Save the table.
   [Staff]
   
   
5. Switch back to Datasheet View.
   The field is now in all lower case letters.
   (Did you widen the field to see all of the characters?)
   
   
6. Click in the cell for the email address for Luis Perez.
   The address shows as you typed it!

7. Experiment: Format forcing lower case
   Type various combinations of lower case and upper case letters in the email address field and then change to a different field. The value is shown in lower case only after you leave the field.
    
   The Format Property does not change what is stored in the field, only the way it is shown.

   When you are ready to continue, you may reset the email address to the original formatting if you wish.

---

Input Mask: Date

The Format and Input Mask Properties for a field can sometimes conflict with each other, or at least be confusing.
 
Next 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. Switch to Design View for the table Staff.

2. Click in the Birthdate field.

3. In the Input Mask property at the bottom of the Design View, click the ellipsis button.
   A wizard dialog opens.

4. Select Short Date.
   

5. Click on the Next > button.
   The dialog shows what the mask code looks like. The mask is different in different versions.

   

   Explanation of the mask
   Where you see 9 in the mask, you may type a digit or a space but you do not HAVE to type in anything at all in that position.

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

   Older versions of Access were very picky about where the spaces went in an input mask, but Access 2007, 2010, 2013, and 2016 are friendlier abou this.

   For Access 2007 and 2010 the Short Date input mask is 99/99/0000;0;_ but for Access 2013 and 2016 it is 00/00/00;0;_
   So the older two versions require a 4-digit year but do not require a month and day. The newer two versions use a two digit year and do require both the month and day. Unexpected!

    Details on custom Input Masks
   
6. Click in the Try It box.
   The cursor is a thick black bar, showing where the character will go when you type. This is different from the way most cursors work these days! The mask uses an underscore to show where a character goes, but the underscores run together. <sigh>
7. Experiment by entering various dates and editing dates. Get familiar with how the mask works.

8. Click on Finish.

   The Design View now shows the mask that the wizard created. It looks a little bit different from what showed in the wizard dialog.

   The 0 that follows the mask itself tells Access to store the symbols with the data. So the slashes are stored, too. The last character is the underscore character. Whatever is in this position is what Access will use as a place holder.
   

9. Switch back to Datasheet View and click in the Birthdate field.

   A mask appears - underscores for the positions to type characters and slash characters. The cursor is a thick black bar, showing where the next character that you type will go.

   The mask is a bit different in the different versions, so the underscores are different in the cell.

   Problem: Mask does not appear
   Did you click in the field or did you use TAB or arrow keys to get into the field?
   Solution: Start typing anyway. Once you start typing the mask appears.
   

10. In the Birthdate field type February 14, 1954 instead of just numbers.
    Ding goes the error sound! Letters are not allowed by the input mask.

11. Click in the Birthdate field again and type 2141954.
    Before you leave the field, the mask displays what you typed as 21/41/954_ for Access 2007 and 2010 or 21/41/95 for Access 2013 and 2016 (with an error ding when you tried to type 4). What is the 21st month? Do any months have 41 days?

    The problem is that February's month number is 2 and we did not include a leading space or a zero. Access does not know what to do.
    

12. Press the TAB key to move out of the field.
    Access won't accept what you typed. Your date is not a real date and Access recognized that when it tried to save it. How smart... and helpful!

    In the next steps, versions are different.

    Access 2007, 2010: A message appears that says your value is not appropriate.

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

    • In the Birthdate field type 2/14/1954, including the slashes, starting from the left. Watch the mask as you type!
      This looks good so far. Typing the slash moved the typing to the next section of the date.

    • 

      Press the right arrow key or TAB key to move to the next field.
      The field is reformatted using the default Date/Time format, instead of the Input Mask. The default format is Short Date. Access 2007 and 2010 do not show a leading zero and show a 4-digit year.

    Access 2013, 2016: An Error Options button appears and opens to the message - The value does not match the Date/Time data type. This menu has options - Enter a new value or Convert to Text data type.

    • Click on 'Enter new value'.
      The current value is highlighted, ready for your typing.
    • Type 2/14/54, including the slashes, starting from the left. Watch the mask as you type!
      This looks good so far. Typing the slash moved the typing to the next section of the date.
    • Press the right arrow key or TAB key to move to the next field.
      Access 2013 and 2016 show a leading zero but only a two digit year.

    All of these versions understand that 2_ is supposed to be 02. This is a big improvement over earlier versions.

    Point of Confusion: Older versions of Access were VERY picky about putting in leading spaces. But with Access 2007, 2010, 2013, and 2016 you can now relax a bit about spaces. 02 and 2_ and _2 are treated the same once you leave the field and the Short Date format is applied by the Format property.

---

Using an Input Mask: Edit a Value (Date)

To Edit a Value: Click on the spot in cell where you want to start editing. If you use TAB or an arrow key to move the focus into a cell, the whole value is selected. Once you start typing, the previous value is completely gone.

Cursor Location: When you click on a cell, the cursor shows up where you clicked, which might not be at the beginning of the value.

Cursor Behavior in a Mask: The cursor is actually selecting a character. It is not a line between characters like it is in normal text. You can get a normal cursor at the right end of a mask.

Drag in value with an Input Mask: If the focus is outside the cell and you drag in a cell to select in a value and there is an input mask, what is selected may not be what you thought you selected. Input masks can show characters and spaces that do not show when you leave the cell.

1. While still in Datasheet View, drag across the 2 in the Birthdate to select it.
   The mask shows up again. Did you get the 2 selected or is the cursor on the blank space? You have to look carefully after you select to see what you really caught.

2. Type a 3 and then click in another field.  

3. Drag to select the last 4 and then press the DELETE key.
4. 

   TAB to the next field.

   Error! You get a message that your value is not appropriate.

   The mask requires the digit that you deleted. For Access 2007 and 2010, the year must have four digits. For Access 2013, 2016, the year must have two digits.

5. Click OK to close the dialog.
6. Click in the date again, on the 5.
7. Type 4.
   This replaces the existing characters instead of inserting them in front or behind. Did you expect that?
8. Correct your typing to return the year to the original form, either 1954 or 54, depending on your version of Access.
   

---

Create a Custom Input Mask

Access provides a wizard for creating some common input masks or 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 or it can allow any kind of character.

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

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 new data or try to edit data will a mismatch 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 AA means that the user is required to enter exactly two letters, instead of a number or another character. There are no literal characters in this mask like slashes in a date or parentheses and hyphens in a phone number, so the second section of the definition is blank. The third section of the mask says that the @ symbol will be the placeholder. This symbol is easier to see than an underscore, but might be confusing to a new user.
    
   Caption: The value in the Caption property could be State/Province instead of the name of the field (StateOrProvince). With a caption you can use characters that are not allowed or are not wise to use in the name of a field, like the slash /character.
   

4. Save the table.  
   [Staff]

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 only two characters? Probably.
   Is it obvious that they must be letters and not numbers or other characters? Probably not!

   Problem: Whole cell was selected and the mask does not show.
          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: Check the Design View again for the Input Mask property.
   

7. Type Tenn, which is a common abbreviation for the state of Tennessee, USA.
   (Yes, I know that the address in this record is in Argentina. We are experimenting here!)

   Only the first two characters were kept. Did you hear error dings? You won't if the computer's sounds are turned off, which is common in computer labs.
   

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.

---

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.
   [Staff]
   
   

4. Switch back to Datasheet view.
   The text is now in upper case, all capital letters, like the postal service wants. A Format rule will be applied to existing data but an Input Mask will not be applied.
    
   Of course since there is no state TN in Argentina, we have created 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. Click in the field StateOrProvince again.
   The letters return to the way you typed them in, lower case.

6. Delete the tn.
   The Input Mask shows again.

7. Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.
   You will just have to type carefully.

8. Save the table.
   [Staff]