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, then you’ll enjoy this feature.

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

Figure 3-1. The Home → Font 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’s 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.) 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 that it’s just before the FreshPastaPreference 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 dialog box 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’s 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’s 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 across the rest.) Then, right-click your selection, and then choose Hide Columns. 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 Columns. Access then shows the Unhide Columns dialog box (Figure 3-5).

Figure 3-5. Using the Unhide Columns dialog box, you can choose to make hidden columns reappear, and (paradoxically) 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.

If you add a new record while columns are hidden, you can’t supply a value for that field. The value starts out either empty or with the default value (if you’ve defined one for that field, as described in Section 4.1.2). If you’ve hidden a required field (Section 4.1.1), 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 Columns.

Figure 3-6. Top: In this example, the FirstName and LastName field are frozen. They appear initially at the left. (The ribbon’s collapsed in this figure to make more room, as described in Section 1.4.2.) 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 using the column reordering trick in Section 3.1.2. Keep in mind that while a column’s frozen, you can’t drag it to a different place.

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

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.

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 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 in Section 3.2.2.

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

In an unsorted table, records are ordered according to when they were created, so that the oldest records are at the top of the datasheet, and the newest at the bottom. Sorting doesn’t change how Access stores records, but it does change the way they’re displayed.

Sorting is a one-time affair. If you edit values in a sorted column, then 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 resort the table. Similarly, any new records 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.

In order to filter records, you specify a condition that the record must meet in order 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. Figure 3-9 shows an example where a sort and filter are being used at the same time.

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.

Note

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

Not all data types support filtering. Data types that do include Number, Currency, AutoNumber, Text, Hyperlink, Date/Time, and Yes/No. Memo 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, as 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 remove your filters, choose Home → Sort & Filter → Remove Filter.

Tip

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 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’s 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’s named according to the data, so 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 PurchasePrice 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, then 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 chose from a huge list of built-in options, like Yesterday, Last Week, Next Month, Year to Date, First Quarter, and so on.

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’s 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 next to 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.

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.

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, then 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.

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

Access starts searching from the current position. If you’re using the standard search direction (Down), Access moves from left to right in the current record, and then down from record to record until it finds a match.

When Access finds a match, it highlights the value. You can then click Find Next to look for the next match, or Cancel to stop searching.

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 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 text and memo fields. Numbers, dates, and everything else get 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 dialog box informs you that all your data’s been checked. If your table passes the spell check, then this dialog box is the only feedback you receive. On the other hand, if Access discovers any potential spelling errors during its check, then 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. Unlike Microsoft Word, Access doesn’t let you edit your file while the Spelling window’s active. You have to either click one of the options on the Spelling window—for example, clicking Change to replace the misspelled word with the first suggested spelling—or cancel the spell check.

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

On the other hand, if the spell checker’s 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 Office button → Access 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’s 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, then 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, then 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, then Access ignores words that appear to be file paths (like c:Documents and Settings) or Web site 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, then 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’s underway. To do so, click the Custom Dictionaries button, which shows the Custom Dictionaries dialog box (Figure 3-17).

Figure 3-17. Top: Using the Custom Dictionaries dialog box, you can delete the current custom dictionary, add new ones, or edit the word list by hand. Bottom: If you click Edit Word List, then you see all the words in your custom.dic file. 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 Dan_Quayle, you’ll probably find the custom dictionary in the folder C:Documents and SettingsDan_QuayleApplication DataMicrosoftUProof. One side effect of this system’s 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 dialog box).

As you type text in a field, AutoCorrect cleans up behind you, correcting things like miscapitalized 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’s harmless and even occasionally useful, as it can spare you from delivering minor typos in a major report. But if you need to type irregularly capitalized words, or if you have a garden-variety desire to rebel against standard English, you can turn off some or all of the AutoCorrect actions.

To set AutoCorrect options, choose Office button → Access 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 “AutoCorrection settings” button.

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

Figure 3-18. 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’s 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:

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’s genuinely usable. To preview a table, open it (or select it in the navigation pane), and then choose Office button →Print →Print Preview.

The print preview shows a picture of what your data will look like once it’s committed to paper. Unlike the datasheet view, the print preview paginates your data (Figure 3-20). You see exactly what fits on each page and how many pages your printout requires (and what content shows up on each page).

Figure 3-20. This table’s too wide to fit on one sheet of paper, so some of the columns are relocated to a second page.

If you decide you’re happy with what you see, then you can fire off your printout by choosing Print Preview → Print → Print from the ribbon. This opens the familiar Windows Print dialog box, where you can pick a printer and seal the deal.

When you’re finished looking at the print preview window, choose 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.

Based on the limited page layout options, you might assume that there’s not much you can do 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.