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 28-1).

Figure 28-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 right to left, 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 28-2.

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.

Figure 28-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 FreshPastaPreference field.

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 28-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 28-4).

Figure 28-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 28-5).

Figure 28-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 on Setting Default Values). If you’ve hidden a required field (Preventing Blank Fields), 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 28-6). To freeze a column (or columns), select them, right-click the column header, and then choose Freeze Columns.

Figure 28-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 on Managing Database Objects.) 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 size 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 on Rearranging Columns. Keep in mind that while a column’s frozen, you can’t rag 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 28-8).

Depending on the data type of field, you’ll see different sorting options, as explained in Table 28-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.

Figure 28-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 on Quick filters.

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.

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

Aquick 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 28-9 shows an example where a sort and filter are being used at the same time.

Figure 28-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 dropdown 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 dropdown 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 28-10).

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 utility is what gives the filter “by selection” feature its name.

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

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

Figure 28-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 28-12 shows an example.

Figure 28-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 28-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 28-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:

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 28-15). You see exactly what fits on each page and how many pages your printout requires (and what content shows up on each 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.

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

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.