Text fields

Text fields can be formatted using four characters that affect capitalization, spacing, and punctuation.

Access does not have predesigned formats for text fields, but you can make your own. Just string together some special characters to construct a formatting string that Access can use to display the text in a standardized way.

Table 7-1 lists the special characters that you can use to build your text formats.

Here’s what you need to know about the formatting codes in Table 7-1.

Number and currency fields

Microsoft makes it easy for you to apply numeric formats to your numeric fields. They built the seven most common formats into a drop-down menu in the Format property row.

To set a Number or Currency field format, follow these steps:

1. With your table in Design view, click the Format text box for the field you’d like to format.

2. Click the down arrow that appears at the right side of the box and select a format for your field.

   Figure 7-3 shows the drop-down menu, which is divided in half:

   • The left side shows each format’s given name.
   • The right side shows a sample of how each format looks.

Numeric formats change only the appearance of the number, not the number as it is stored. So if you select the Single Field Size and choose Standard to format a field that contains the number 1.235678, you see 1.24 on the screen — but Access stores 1.235678 in the field. Any calculations done with the numbers in that field will use the actual typed-in number, not the formatted number seen onscreen.

The following sections describe the numeric formats built into Access.

Date/time fields

Microsoft provides you with a drop-down menu full of ready-to-use date and time formats. Here’s how to apply a date/time format to a field:

1. With your table in Design view, click the Format text box for the field you’d like to format.

2. Click the down arrow that appears on the right side of the text box.

   The menu shown in Figure 7-4 drops down to serve you.

3. Select the format you want to use.

Keep these tips in mind when you apply a date/time format:

• When you use one of the longer formats, such as General Date or Long Date, make sure that the datasheet column is wide enough to display all the information. Otherwise, Access will fill the field with number symbols (#), making the date unreadable.
• If more than one person uses the database, choose a format that provides more information, not less information.

My clients often ask me to provide a date in the m/d/yy format with a two-digit year (such as 10/1/16 rather than 10/1/2016). To display a date with a two-digit year, type the following in the format box: m/d/yy.

Yes/No fields

You can say only so much about a field with three options. Oddly, Yes/No fields are set to the Yes/No formatting by default.

If you want the ability to type Yes/No, True/False, or On/Off in the field, make sure that Display Control in the Lookup tab (next to the General tab) is set to Combo Box. Otherwise, you’ll have check boxes in your field (because Check Box is the default display for a Yes/No field).

Allowable Yes/No field entries

Here’s what you can type in a Yes/No field (see Figure 7-5):

• Yes and No (this is the default)
• On and Off
• True and False

Yes and No is the default, but you can change how a Yes/No field formats its content. Here’s how:

1. With your table in Design view, click the Format text box for the field you’d like to format.

2. Click the down arrow that appears on the text box’s right side.

   The menu of three Yes/No formats drops down for your selection.

3. Select the format you want to use.

You really need to put a mask on those fields

An input mask is a series of characters that tells Access what data to expect in a particular field — and actually prevents users from typing data that does not fit the mask. If you want a field to contain all numbers and no letters, an input mask can do the job. It can also do the reverse (all letters and no numbers) and almost any combination in between. Short Text, Number, Date/Time, and Currency field data types accept input masks.

Formatting (shown previously in this chapter) can make some data entry errors visible, but formatting doesn’t block errors. Input masks, on the other hand, keep that bad data out.

Input masks are stored in the Input Mask property box of the field’s General tab. (The beginning of this chapter shows the steps to follow to access the General tab.)

Add these masks to fields that contain dates, times, phone numbers, Social Security numbers, passwords, and zip codes, among other things. You’ll be so glad you did. If you don’t, expect to see plenty of phone numbers like 111-123 and zip codes like 0854.

Input masks work best with short, consistent data. Numbers and number-and-letter combinations that follow a consistent pattern are excellent candidates. Phone numbers, dates, and zip codes are common examples of data items that follow a consistent pattern.

You create an input mask in one of two ways:

• Ask the Input Mask Wizard for help.

  The Input Mask Wizard can’t possibly contain every mask for every situation. It only knows about text and date fields, and offers just a few options.

  Always start with the wizard. If it doesn’t have your solution, then you need to manually build the mask.

• Type the mask manually.

  Create the mask manually if your data follows a consistent pattern (such as a six-digit part number) that isn’t a choice offered by the Input Mask Wizard.

Using the Input Mask Wizard

The Input Mask Wizard gladly helps if you’re making a mask for text fields (such as those containing phone numbers, Social Security numbers, and United States zip codes) or simple date and time fields.

If your data doesn’t fit one of the masks that the wizard provides yet follows a consistent pattern, check the next section, which shows how to create a mask manually.

To ask for the wizard’s help, follow these steps:

1. Open the database file that contains the data you want to mask. From the Navigation pane, right-click the table you want to modify and choose Design View.

   The table flips into Design view.

2. Click the name of the field that will receive the input mask.

   You can use the wizard only with text and date/time fields.

   The General tab in the Field Properties section (the bottom half of the window) displays the details of the current field.

3. Click the Input Mask box.

   The cursor monotonously blinks away in the Input Mask box. To the right of the box, a small button with three dots appears. That’s the Builder button, which comes into play in the next step.

4. Click the Builder button.

   The wizard appears, offering a choice of input masks, as shown in Figure 7-6.

5. Scroll through the list of input masks to find what you want.

6. Click the input mask you want.

   To play with the mask a bit and see how it works, click the Try It area at the bottom of the dialog box and then type a sample entry.

7. Click Finish to close the wizard and use the mask with your field.

   If you click Next instead of Finish, the wizard gives you more options, but I recommend avoiding them. (The sidebar “The rest of the Input Mask Wizard” has the details.)

   The chosen mask appears in the Input Mask text box in the table’s Field Properties section, as shown in Figure 7-7.

Making a mask by hand

It’s not uncommon to need a mask that the Input Mask Wizard doesn’t provide. If your fingers can string together a seemingly nonsensical string of characters on the keyboard, then you can make your own input mask. The trick is making sense out of all the nonsensical characters.

Table 7-2 shows the codes you can use in an input mask; each code has an explanation of the character(s) that it represents:

• Required Code: Users must type that type of character (whether they actually want to or not).
• Optional Code: Users can type or not type the kind of character mentioned in the first column. For example, if your input mask is 99/99/0000;0;_, you can type 1/1/2016 or 01/01/2016 but you can’t type 1/1/16 or 01/01/16.

Kind of Characters	Required Code	Optional Code
Digits (0 to 9) only.	0 (zero)	9
Digits and + and -.	(not available)	# (U.S. pound sign)
Letters (A to Z) only.	L	? (question mark)
Letters or digits only.	A	a (must be lowercase)
Any character or space.	& (ampersand)	C
Any character typed into the mask is filled in from right to left instead of the usual left to right.	!	None
Any literal character.	( for example, * displays as just *)	None
All characters typed into the mask are forced to lowercase.	<	None
All characters typed into the mask are forced to uppercase.	>	None

You must use the input mask codes to design an input mask.

Designing an input mask

Before you can create a mask, you must determine what mask codes you’ll need to build the mask. Here’s how:

1. On a piece of paper, write several examples of the data that the mask should allow into the table.

   If the information you’re storing has subtle variations (such as part numbers that end in either a letter/number or letter/letter combination), include examples of the various possibilities so that your input mask accepts them all. You can’t build a mask if you don’t know your data.

2. Write a simple description of the data, including which elements are required and which are optional.

   For example, if your sample is a part number that looks like 728816ABC7, write six numbers, three letters, one number; all parts are required.

   Remember to allow for the variations, if you have any. The difference between one number and one letter or number can be crucial.

   If you need to include a special character in your mask, like a dash or parentheses or a combination of static characters, use this list for guidance:

   • Dash, slash, or parenthesis characters: Put a backslash () in front of it, such as - for a dash.

   • Multiple characters: Put quotation marks around them.

     For example, an area code may be separated from the rest of the number by both a parenthesis and a space, like this:

     (567) 555-2345

     The corresponding mask has quotes around the parenthesis and the space, like this: !(999") "999-9999.

     The phone-number mask also begins with an exclamation point. The exclamation point forces the typed data to fill the mask from right to left rather than left to right (the default). What’s the big deal about that? Some phone numbers don’t require an area code; others do. Suppose you have to type a seven-digit phone number. If not for the right-to-left entry, you’d have to move the cursor past the area code (___) placeholder part of the mask to get to the beginning of the seven-digit part.

   If your field includes letters and you want them to be stored as all-uppercase, add a greater-than symbol (>) to the beginning of your mask. To store the letters as all-lowercase, use a less-than symbol (<) instead.

3. Write the mask codes that represent the elements you’ve written in Step 2.

   If (for example), you wrote “six numbers, three letters, one number; all parts are required” in Step 2, then you need the mask codes 000000LLL0. Refer to Table 7-2.

Putting on your input mask

Now that you have your mask written on paper, it’s time to enter it in Access. Here’s how:

1. With the database file open, right-click the table you want to work with and then choose Design View from the shortcut menu.

   The table flips to Design view.

2. Click the name of the field you want to adjust.

3. Click the Input Mask box.

   The cursor blinks in the Input Mask box.

4. Carefully type your finished mask into the Input Mask area of the Field Properties section (as shown in Figure 7-8).

   If you don’t know what to type here, see the preceding section.

5. Click the View button on the Ribbon to switch to Datasheet view and place the cursor in the masked field to check out your new mask.

   When prompted to Save, click Yes so you don’t lose your work.

   When you’ve entered the mask and have saved the table, try these tests:

   1. Type something unacceptable into the masked field.

      The input mask should prevent you from typing an incorrect value (see Figure 7-9).

   2. Try an acceptable entry.

      The mask should accept your entry.

   3. Try all the variations you identified in the mask-planning process.

      All should be accepted by the mask. If they are not, switch back to Design view and tweak your mask until all possible variations of your entry are acceptable to the mask.

If you’re adding a mask to an existing table with data, Access doesn’t report to you on existing records that fail the mask; it gives that data a free pass to exist as typed. To enforce the mask on existing records, you’ll need to repeat these steps for each record:

1. Click the field in the record.

2. Edit the data.

   You can delete the last character and then retype it.

   When you move the cursor out of the field, you’ll see the warning if the data doesn’t comply with the mask.

To require or not to require

On many occasions, you will not want a record typed until all the facts are in. For example, you certainly wouldn’t want an order typed without an order date, customer, and product information. The Required property prevents records that are missing essential data from being saved to a table.

The Required property has two settings:

• Yes: The user cannot save the record without putting something in the field.
• No: Anything goes. (This is the default.)

To require data entry in a field, follow these simple steps:

1. While in a table’s Design view, click the field in which you want to require data entry.

2. Click in the Required box on the General tab in the Field Properties section.

   An arrow appears at the end of the box. By default, the box reads No.

3. Click the arrow and select Yes from the list that appears, as shown in Figure 7-10.

   Watch out, the field is now required!

4. Click the View button on the Ribbon to switch to Datasheet view and then test your work.

   Type a new record, omitting data entry in the required field. You should see a message box gently reminding you for forgetting the required data.

   Don’t get overzealous with the Required property and set it to Yes for nonessential fields. For example, a volunteer without an email address is usually better than no volunteer at all.

Making your data toe the line with validation

With a validation, Access tests the incoming data to make sure that it’s what you want in the table. If the data isn’t right, the validation displays an error message (you get to choose what it says) and makes the user try the entry again.

Like the other options in this chapter, validations are stored in the General tab of the Field Properties area. Two options relate to validations:

• Validation Rule: This rule is the validation itself.
• Validation Text: The text is the error message you want Access to display when some data that violates the validation rule is typed.

Validations work best with number, currency, and date fields. Creating a validation for a text field is possible, but the validations tend to get very complicated very fast.

Tables 7-3 and 7-4 contain some ready-to-use validations that cover the most common needs. These are ready for you to type into the General tab of the Field Properties area.

Here’s how to enter a validation rule:

1. With the database file open, right-click the table you want to work with and choose Design View from the shortcut menu.

   The table flips to Design view.

2. Click the name of the field you want to adjust.

3. Click the Validation Rule box.

   The cursor blinks in the Validation Rule box.

4. Type the validation rule that matches your data.

   For example, if you want to allow only numbers between 0 and 1,000 in the field, type >0 AND <1000.

5. Click in the Validation Text field.

   The cursor blinks in the Validation Text box.

6. Type the message you’d like the user to see if he breaks the validation rule.

   Keep it short and simple. For my example in Step 4, you might type Please enter a number greater than 0 and less than 1,000.

When you apply a validation rule to a field, watch out for these gotchas:

• When using AND, both sides of the validation rule must be true before the rule is met.
• With OR, only one side of the rule needs to be true for the entire rule to be true.

• Be careful when combining >= and <=. Accidentally coming up with one that can’t be true (such as <= 0 AND >= 100) is too easy!

You can test your new validation rules against existing table data by clicking the Test Validation Rules button in the Tools group of the Ribbon’s Design tab. Just follow the prompts in the resulting message boxes (to save your table, for example), and you’ll know if your data likes the new rules!