Access lets you format the datasheet with eye-catching colors and fonts. Do these options make any difference to the way the datasheet works? Not really. But if your computer desktop looks more like a ’60s revival party than an office terminal, you’ll enjoy this feature.

To find the formatting features, look at the ribbon’s Home→Text Formatting section (see Figure 3-1).

Figure 3-1. The Home→Text Formatting section lets you change the text font and colors in the entire datasheet. The most practical frill is the ability to turn off some or all of the gridlines and use alternating row colors to highlight every other row, as shown here.

Every formatting change you make affects the entire table. You may think it’s a nifty idea to apply different formatting to different columns, but Access doesn’t let you. If this limitation is frustrating you, be sure to check out forms and reports later in this book. Both are more complicated to set up, but give you more formatting power.

The fields in the datasheet are laid out from left to right, in the order you created them. Often, you’ll discover that this order isn’t the most efficient for data entry.

Imagine you’ve created a Customers table for a novelty pasta company. When a new customer registration ends up on your desk, you realize that the registration form starts with the name and address information, and then includes the customer’s pasta preferences. Unfortunately, the fields on the datasheet are laid out in a completely different order. From left to right, they’re arranged like this: ID, FreshPastaPreference, DriedPastaPreference, FirstName, LastName, Street, City, State, Country. This organization isn’t as crazy as it seems—it actually makes it easier for the people filling pasta orders to quickly find the information they want. But because of this ordering, you need to skip back and forth just to enter the information from a single registration.

Fortunately, you can solve this problem without redesigning the table. Drag the columns you want to move to new positions, as shown in Figure 3-2.

Figure 3-2. To move a column, click the column header once to select that column. Then, drag the column header to its new location. In this example, the FirstName field is about to be relocated so that it’s just before the Fresh-PastaPreference field.

The best part of this approach is that you don’t need to modify the database’s actual structure. If you switch to Design view after moving a few columns, you’ll see that the field order hasn’t changed. In other words, you can keep the exact same physical order of fields (in your database file), but organize them differently in Datasheet view.

As you cram more and more information into a table, your datasheet becomes wider and wider. In many cases, you’ll be frustrated with some columns hogging more space than they need and others being impossibly narrow.

As you’d expect, Access lets you tweak column widths. But you probably haven’t realized how many different ways you can do it:

Figure 3-3. The Column Width window lets you set an exact width as a number. (The number doesn’t actually have a concrete meaning—it’s supposed to be a width in characters, but because modern Access uses proportional fonts, different characters are different sizes.) You can also turn on the Standard Width checkbox to reset the width to the standard narrow size, or click Best Fit to expand the column to fit its content (just as when you double-click the edge of the column).

Just as you can resize columns, you can also resize rows. The difference is that Access makes sure all rows have the same size. So when you make one row taller or shorter, Access adjusts all the other rows to match.

You’ll mainly want to shrink a row to cram more rows into view at once. You’ll want to enlarge a row mostly to show more than one line of text in each text field (see Figure 3-4).

Figure 3-4. If a row is large enough, Access wraps the text inside it over multiple lines, as shown here with the Description column.

Many tables contain so many columns that you can’t possibly fit them all into view at the same time. This quality is one of the drawbacks to the datasheet, and often you have no choice but to scroll from side to side.

However, in some situations, you may not need to see all the fields at once. In this case, you can temporarily hide the columns that don’t interest you, thereby homing in on the important details without distraction. Initially, every field you add to a table is out in the open.

To hide a column, select the column by clicking the column header. You can also select several adjacent columns by clicking the column header of the first, and then dragging the mouse cursor across the rest. Then, right-click your selection and choose Hide Fields. The column instantly vanishes from the datasheet. (This sudden disappearance can be a little traumatic for Access newbies.)

Fortunately, the field and all its data remain just out of sight. To pop the column back into view, right-click any column header and choose Unhide Fields. Access then shows the Unhide Columns window (Figure 3-5).

Figure 3-5. Using the Unhide Columns window, you can choose to make hidden columns reappear, and (despite the name) you can hide ones that are currently visible. Every column that has a checkmark next to it is visible—every column that doesn’t is hidden. As you change the visibility, Access updates the datasheet immediately. When you’re happy with the results, click Close to get back to the datasheet.

At the bottom of the field list in the Unhide Columns window, you’ll see an entry named “Click to Add.” This “field” isn’t really a field—it’s the placeholder that appears just to the right of your last field in Datasheet view, which you can use to add new fields (Creating a Simple Table). If you’re in the habit of adding fields by using Design view, you can hide this placeholder to free up some extra space.

If you add a new record while columns are hidden, you can’t supply a value for that hidden field. The value starts out either empty or with the default value (if you’ve defined one for that field as described on Setting Default Values). If you’ve hidden a required field (Data Integrity Basics), you receive an error message when you try to insert the record. All you can do is unhide the appropriate column, and then fill in the missing information.

Even with the ability to hide and resize columns, you’ll probably need to scroll from side to side in a typical datasheet. In this situation, you can easily lose your place. You might scroll to see more information in the Contacts table, but then forget exactly which person you’re looking at. Access has one more feature that can help you by making sure important information is always visible—frozen columns.

A frozen column remains fixed in place at the Access window’s left side at all times. Even as you scroll to the right, all your frozen columns remain visible (Figure 3-6). To freeze a column (or columns), select them, right-click the column header, and then choose Freeze Fields.

Figure 3-6. Top: In this example, the First-Name and LastName field are frozen. They appear initially at the left. (The ribbon is collapsed in this figure to make more room, as described on page 52.) Bottom: When you scroll to the side to see more information, the FirstName and LastName columns stay put.

Frozen columns must always be positioned at the left side of the datasheet. If you freeze a column that’s somewhere else, Access moves it to the left side and then freezes it. You can move it back after you unfreeze the column by using the column-reordering trick on Rearranging Columns. Keep in mind that while a column is frozen, you can’t drag it to a different place.

To unfreeze columns, right-click a column header, and then choose Unfreeze All Fields.

In some cases, you can most easily make sense of a lot of data by putting it in order. You can organize a customer list by last name, a product catalog by price, a list of wedding guests by age, and so on. Sorting doesn’t change how Access stores records, but it does change the way they’re displayed.

To sort your records, pick a column you want to use to order the records. Click the drop-down arrow at the right edge of the column header, and then choose one of the sort options at the top of the menu (see Figure 3-8).

Figure 3-8. This Short Text field gives you the choice of sorting alphabetically from the beginning of the alphabet (A to Z) or backward from the end (Z to A). The menu also provides filtering options, which are described on page 107.

Depending on the data type of the field, you’ll see different sorting options, as explained in Table 3-1. (You can also apply the same types of sort by using the commands in the ribbon’s Home→Sort & Filter section.)

Sorting is a one-time affair. If you edit values in a sorted column, Access doesn’t reapply the sort. Imagine you sort a list of people by FirstName. If you then edit the FirstName value for one of the records, changing “Frankie” to “Chen,” Access doesn’t relocate the row to the C section. Instead, the changed row remains in its original place until you re-sort the table. Similarly, any new records that you add stay at the end of the table until the next sort (or the next time the table is opened). This behavior makes sense. If Access relocated rows whenever you made a change, you’d quickly become disoriented.

...
Smith, Star
Smith, Susan
Smith, Sy
Smith, Tanis
...

...
Smith, Star
Smith, Susan K
Smith, Susan P
Smith, Sy
...

In a table with hundreds or thousands of records, scrolling back and forth in the datasheet is about as relaxing as a pneumatic drill at 3:00 a.m. Sometimes, you don’t even need to see all the records at once—they’re just a finger-tiring distraction from the data you’re really interested in. In this case, you should cut the datasheet down to just the records that interest you, with filtering.

To filter records, you specify a condition that the record must meet to be included in the datasheet. For example, an online store might pick out food items from a full product catalog, a shipping company might look for orders made last week, and a dating service might hunt down bachelors who don’t live with their parents. When you apply a filter condition, you end up hiding all the records that don’t match your requirements. They’re still in the table—they’re just tucked neatly out of sight.

Access has several different ways to apply filters. In the following sections, you’ll start with the simplest, and then move on to the more advanced options.

Quick Filters

A quick filter lets you choose what values you want to include and which ones you want to hide, based on the current contents of your table. To apply a quick filter, choose the column you want to use, and then click the drop-down arrow at the column header’s right edge. You’ll see a list of all the distinct values in that column. Initially, each value has a checkmark next to it. Clear the checkmark to hide records with that value. If you want to hide everything except for a few specific values, click to remove the checkmark next to “(Select All),” and then add a checkmark next to the ones you want. Click OK to apply your filter.

Figure 3-9 shows an example where a sort and filter are being used at the same time. When a column is using filtering, Access adds a funnel icon to the right side of the column header.

Figure 3-9. This list of eligible bachelors is sorted first by height (in descending largest-to-smallest order), and then filtered to include only those hopefuls who live in the state of New York. A checkmark indicates that records that have this value are included in the datasheet. Others are hidden from view.

Tip

To remove all the filters on a column (and show every record in the datasheet), click the drop-down button at the right edge of the column header, and then choose “Clear filter from.”

Not all data types support filtering. Data types that do include Number, Currency, AutoNumber, Short Text, Hyperlink, Date/Time, and Yes/No. Long Text fields don’t support quick filters (because their values are typically too large to fit in the drop-down list), but they do support other types of filters.

You can apply quick filters to more than one column. The order in which you apply the filters doesn’t matter, because all filters are cumulative, which means you see only records that match all the filters you’ve set. You can even use quick filters in combination with the other filtering techniques described in the following sections. To temporarily remove a filter, choose Home→Sort & Filter→Toggle Filter. Click Toggle Filter again to put your filter back into action.

Note

Quick filters work best if you have a relatively small number of distinct values. Limiting people based on the state they live in is a great choice, as is the political party they support or their favorite color. It wouldn’t work as well if you wanted to cut down the list based on birth date, height, or weight, because there’s a huge range of different possible values. (You don’t need to give up on filtering altogether—rather, you just need to use a different type of filter.)

Filter by Selection

Filter by selection lets you apply a filter based on any value in your table. This choice is handy if you’ve found exactly the type of record you want to include or exclude. Using filter by selection, you can turn the current value into a filter without hunting through the filter list.

Here’s how it works. First, find the value you want to use for filtering in the datasheet. Right-click the value, and then choose one of the filter options at the end of the menu (see Figure 3-10).

Figure 3-10. Depending on the data type, you see slightly different filtering options. For a Short Text field (like the City field shown here), you have the option to include only the records that match the current value (Equals “Chicago”), or those that don’t (Does Not Equal “Chicago”). You also have some extra filtering options that go beyond what a quick filter can do—namely, you can include or exclude fields that simply contain the text “Chicago.” That filter condition applies to values like “Chicagoland” and “Little Chicago.”

All data types that support filtering allow you to filter out exact matches. But many also give you some additional filtering options in the right-click menu. Here’s what you’ll see for different data types:

• Text-based data types. You can filter values that match exactly, or values that contain a piece of text.

• Numeric data types. You can filter values that match exactly, or numbers that are smaller or larger than the current number.

• Date data types. You can filter values that match exactly, or dates that are older or newer than the current date.

Finally, to get even fancier, you can create a filter condition using only part of a value. If you have the value “Great at darts” in the Description field in your table of hopeful bachelors, you can select the text “darts,” and then right-click just that text. Now you can find other fields that contain the word “darts.” This ability is what gives the filter “by selection” feature its name.

Access makes it easy to switch filtering on and off at a moment’s notice. Figure 3-11 shows how.

Figure 3-11. Right next to the navigation controls at the bottom of your datasheet is a Filtered/Unfiltered indicator that tells you when filtering is applied. You can also use this box to quickly switch your filter on and off—clicking it once removes all filters, and clicking it again reapplies the most recent set of filters.

Filter by Condition

So far, the filters you use have taken the current values in your table as a starting point. But if you’re feeling confident with filters, you may be ready to try a more advanced approach: filtering by condition. When you use a filter by condition, you can define exactly the filter you want.

Imagine you want to find all the rare wine vintages in your cellar with a value of more than $85. Using the filter-by-selection approach, you need to start by finding a wine with a value of $85, which you can use to build your condition. But what if there isn’t any wine in your list that has a price of exactly $85, or what if you just can’t seem to find it? A quicker approach is defining the filter condition by hand.

Here’s how it works. First, click the drop-down arrow at the right edge of the column header. But instead of choosing one of the quick filter options, look for a submenu with filtering options. This menu is named according to the data, so Short Text fields include a Text Filters option, number fields have a Number Filters option, and so on. Figure 3-12 shows an example.

Figure 3-12. Top: With a numeric field like this Purchase-Price field, filtering by condition lets you look at values that fall above a certain minimum. Bottom: Once you’ve chosen the type of filter you want, you need to supply the information for that filter. If you choose Greater Than, you need to supply the minimum number. Records that are equal to or larger than this value are shown in the datasheet.

Here’s a quick overview that describes the extra options you get using filter by condition, depending on your data type:

• Text-based data types. All the same options as filter by selection, plus you can find values that start with specific text, or values that end with certain text.

• Numeric data types. All the same options as filter by selection, plus you can find values that are in a range, meaning they’re greater than a set minimum but smaller than a set maximum.

• Date data types. All the same options as filter by selection, plus you can find dates that fall in a range, and you can choose from a huge list of built-in options, like Yesterday, Last Week, Next Month, Year to Date, First Quarter, and so on.

POWER USERS’ CLINIC: Filters vs. Queries

If you use filters frequently, you’re sure to run into a problem. Access stores only one set of filters—the filters you’re currently using. In other words, once you apply a different filter, your original filter is gone, and you need to reapply it from scratch the next time you need it. In most cases, reapplying a filter isn’t difficult. But if you’ve spent a considerable amount of effort crafting the perfect set of filter conditions, and you know you want to use them later, it’s frustrating.

If you find yourself in this situation, you’re overusing filters. Instead of relying on filters to show the information you’re interested in, you’d be better off creating a separate, reusable query. Like filters, queries let you see a subset of your data based on certain conditions. Unlike filters, queries can contain much more sophisticated logic, they can leave out columns you’re not interested in, and Access saves them as separate database objects so you can always reuse them later. You’ll start using queries in Chapter 6.

Access also provides a quick search feature that lets you scan your datasheet for specific information. Whereas filtering helps you pull out a batch of important records, searching is better if you need to find a single detail that’s lost in the mountains of data. And while filtering changes the Datasheet view by hiding some records, searching leaves everything as is. It just takes you to the data you want to see.

The quickest way to search is through the search box near the record navigation controls (see Figure 3-13). Just type in the text you want to find. As you type, the first match in the table is highlighted automatically. You can press Enter to search for subsequent matches.

When performing a search, Access scans the table starting from the first field in the first record. It then goes left to right, examining every field in the current record. If it reaches the end without a match, it continues to the next record and checks all of its values, and so on. When it reaches the end of the table, it stops.

Figure 3-13. Here, a search is being performed for the word “bobblehead.” If you find a match, you can keep searching—just press Enter again to jump to the next match. In this example, pressing Enter sends Access to the next record’s Description field.

If you want to change the way Access performs a search, you’ll need to use the Find feature instead:

The spell-checking functionality in Access is almost exactly the same as in other Office applications like Word—it uses the same dictionary, catches the same sorts of errors, and gives you the option to either ignore things it doesn’t recognize or add them to the dictionary.

The difference is that when you perform a spell check with Access, it examines only the content in Short Text and Long Text fields. Numbers, dates, and everything else gets a pass. Of course, many of your fields are likely to contain text you don’t want to spell check—like names, places, or product titles. You have two ways to handle this. You can perform a spell check on a single field, thereby ignoring everything else. Or, you can start a datasheet-wide spell check, but choose to ignore certain fields on the fly.

Here’s how it works:

When the spell check finishes, a window informs you that all your data has been checked. If your table passes the spell check, this window is the only feedback you receive. On the other hand, if Access discovers any potential spelling errors during its check, it displays a Spelling window (as shown in Figure 3-15), showing the offending word and a list of suggestions.

Figure 3-15. When Access encounters a word it thinks is misspelled, it highlights the word. At that point, you can click one of the options on the Spelling window—for example, click Change to replace the misspelled word with selected suggestion—or type your own correction into the “Not in Dictionary” box.

The Spelling window offers a wide range of choices. If the spell checker is complaining about a word that really is misspelled, you have three options:

On the other hand, if the spell checker is complaining about a word that you don’t want to change, you have a few more possibilities available, by clicking the following options:

Spell-Checking Options

You can control how the spell checker works by setting a few straightforward options. To set these options (or just take a look at them), choose File→Options to show the Access Options window. Then, choose Proofing in the list on the left (Figure 3-16). You can also find the same page of options if you click the Spelling window’s Options button while a spell check is underway.

Figure 3-16. The spell checker options let you specify the language and a few other miscellaneous settings. All spell check settings are language-specific; the last box in the window indicates the language you’re currently using.

Here are the most common spelling options:

• Ignore words in UPPERCASE. If you choose this option, Access doesn’t bother to check any word in all capitals (which is helpful when your text contains lots of acronyms).

• Ignore words that contain numbers. If you choose this option, Access doesn’t check words that contain numeric characters, like “Sales43” or “H3ll0.” If you don’t choose this option, Access checks these entries and flags them as errors, unless you’ve specifically added them to the custom dictionary.

• Ignore Internet and file addresses. If you choose this option, Access ignores words that appear to be file paths (like C:Documents and Settings) or website addresses (like http://FreeSweatSocks.com).

• Flag repeated words. This search finds errors where you inadvertently repeat the same word twice, like like this this.

• Enforce accented uppercase in French. Forces French words to take the accents they should have, even for capital letters (where they look a little weird). English speakers don’t need to worry about this setting.

• Suggest from main dictionary only. If you choose this option, the spell checker doesn’t use words in the custom dictionary as suggestions if it finds an unrecognized word. However, it still accepts a word that matches one of the entries in the custom dictionary.

You can also choose the file that Access uses to store custom words—the unrecognized words that you add to the dictionary while a spell check is underway. To do so, click the Custom Dictionaries button, which shows the Custom Dictionaries window (Figure 3-17).

Figure 3-17. Access starts you off with two custom dictionary files: RoamingCustom.dic (the default) and custom.dic (for backward compatibility with old versions of Office). To add a custom dictionary that already exists, click Add and browse to the file. Or click New to create a new, blank custom dictionary. You can also edit the list of words a dictionary contains (select it and click Edit Word List). Figure 3-18 shows an example of editing the default dictionary.

Figure 3-18. When you click Edit Word List, you see all the words in your custom dictionary. You can add new ones or remove entries that no longer apply.

Note

Custom dictionaries are stored in an account-specific section of your hard drive. For example, if you’re logged in under the user account mitt_romney, you’ll probably find the custom dictionary in the folder C:Usersmitt_romneyAppDataRoamingMicrosoftUProof. One side effect of this system is that custom dictionaries aren’t shared between two people who use different accounts on the same computer (unless you manually add the other user’s dictionary in the Custom Dictionaries window).

As you type text in a field, AutoCorrect cleans up behind you, correcting things like incorrectly capitalized letters and common misspellings. AutoCorrect is such a subtle feature that you might not even realize it’s monitoring your every move. To get a taste of its magic, look for behaviors like these:

For the most part, AutoCorrect is harmless and even useful, because it can spare you from delivering minor typos in a major report. But if you need to type irregularly capitalized words (or just like to rebel against standard English), you can turn off some or all of the AutoCorrect actions.

To set AutoCorrect options, choose File→Options to show the Access Options window. Then, choose Proofing in the list on the left. In the page of settings on the right side, click the “AutoCorrect options” button.

Most of the settings are self-explanatory, and you can turn them off by unchecking them. Figure 3-19 explains the “Replace text as you type” option, which is not just for errors.

Figure 3-19. Under “Replace text as you type” is a long list of symbols and commonly misspelled words (the column on the left) that Access automatically replaces with something else (the column on the right). But what if you want the copyright symbol to appear as a C in parentheses? You can remove individual corrections (select one, and then click Delete), or you can change the replacement text. And you can add your own rules. You may want to be able to type “PESDS” and have Access insert “Patented Electronic Seltzer Delivery System.” Simply type in the “Replace” and “With” text as shown here, and then click Add.

Text content isn’t just about letters, numbers, and punctuation. You also have special symbols that you can’t type directly on your keyboard. One example is the copyright symbol (©), which you can insert into a field by entering the text (C), and letting AutoCorrect do its work. Other symbols, like the Greek theta (θ), aren’t as readily available. To use a symbol like this, you’ll need the help of the Character Map utility.

The Character Map is an often-overlooked tool that lets you see all the characters that a font provides. It’s great for digging out the odd accented é and other non-English characters.

Here’s how you can use the Character Map to add a special character:

Figure 3-21. Here, the Total row shows the average price of all the records in the Dolls table.

The print preview feature in Access gives you the chance to tweak your margins, paper orientation, and so on, before you send your table to the printer. This way, you can make sure the final printout is usable. To preview a table, open it (or select it in the navigation pane), choose File→Print, and then click the Print Preview button.

The print preview shows a picture of what your data will look like once it’s committed to paper. Ordinarily, the print preview shows you a single page of your printout at a time. But to get an overall sense of what’s going on—for example, to see whether all your columns can fit on a single page—it’s a good idea to lay two or more sheets side by side. To see two pages at once, choose Print Preview→Zoom→Two Pages (Figure 3-22). To see more, choose Print Preview→Zoom→More Pages, and then choose the number of pages you want to see at once from the list.

If you decide you’re happy with what you see, you can fire off your printout by clicking the Print button on the ribbon (Print Preview→Print→Print). The familiar Windows Print window opens so you can pick a printer and seal the deal.

When you’re finished looking at the print preview window, click the ribbon’s Close Print Preview button (Print Preview→Close Preview→Close Print Preview), or click one of the view buttons at the Access window’s bottom-right corner to switch to Datasheet view or Design view.

Figure 3-22. Unlike the datasheet view, the print preview paginates your data. You see exactly what fits on each page and how many pages your printout requires (and what content shows up on each page). Print preview shows you that this table is too wide to fit on one sheet of paper, so some of the columns will be relocated to a second page.

Based on the limited page layout options, you might assume that you can’t do much to customize a printout. However, you actually have more control than you realize. Many of the formatting options that you’ve learned about in this chapter also have an effect on your printout. By applying the right formatting, you can create a better printout.

Here are some pro printing tips that explain how different formatting choices influence your printouts:

The only challenge you face when using these settings is the fact that you can’t set them from the print preview window. Instead, you have to set them in the datasheet, jump to the print preview window to see the result, jump back to the datasheet to change them a little bit more, jump back to the print preview window, and so on. This process can quickly get tiring.