Working in Query Datasheet View

When you’re developing an application, you might need to work in table or query Datasheet view to help you load sample data or to solve problems in the queries, forms, and reports you’re creating. You might also decide to create certain forms in your application that display information in Datasheet view. Also, the techniques for updating and manipulating data in forms are very similar to doing so in datasheets-so you need to understand how datasheets work to be able to explain to your users how to use your application. If you’re using Access 2007 as a personal database to analyze information, you might frequently work with information in Datasheet view. In either case, you should understand how to work with data editing, and the best way to learn how is to understand viewing and editing data in Datasheet view.

Before you get started with the remaining examples in this chapter, open  ContactsDataCopy.accdb from your sample files folder. In that database, you’ll find a query named qryContactsDatasheet that we’ll use in the remainder of this chapter. We defined this query to select key fields from tblContacts and display a subdatasheet from tblContactEvents.

Moving Around and Using Keyboard Shortcuts

Open the qryContactsDatasheet query in the  ContactsDataCopy.accdb database. You should see a result similar to Figure 28–31. Displaying different records or fields is simple. You can use the horizontal scroll bar to scroll through a table’s fields, or you can use the vertical scroll bar to scroll through a table’s records.

Figure 28–31: Open the Datasheet view of the qryContactsDatasheet query to begin learning about moving around and editing in a datasheet.

In the lower-left corner of the table in Datasheet view, you can see a set of navigation buttons and the Record Number box, as shown in Figure 28–32. The Record Number box shows the relative record number of the current record (meaning the number of the selected record in relation to the current set of records, also called a recordset). You might not see the current record in the window if you’ve scrolled the display. The number to the right of the new record button shows the total number of records in the current recordset. If you’ve applied a filter against the table (see “Searching for and Filtering Data” on page 961), this number might be less than the total number of records in the table or query.

Figure 28–32: You can navigate through the datasheet records using the navigation buttons and Record Number box.

You can quickly move to the record you want by typing a value in the Record Number box and pressing Enter or by using the navigation buttons. You can also click the Go To command in the Find group on the Home tab on the Ribbon to move to the first, last, next, or previous record, or to move to a new, empty record. You can make any record current by clicking anywhere in its row; the number in the Record Number box will change to indicate the row you’ve selected.

You might find it easier to use the keyboard rather than the mouse to move around in a datasheet, especially if you’re typing new data. Table 28–6 lists the keyboard shortcuts for scrolling in a datasheet. Table 28–7 lists the keyboard shortcuts for selecting data in a datasheet.

Table 28–6: Keyboard Shortcuts for Scrolling in a Datasheet
Open table as spreadsheet

Keys	Scrolling Action
Page Up	Up one page
Page Down	Down one page
Ctrl+Page Up	Left one page
Ctrl+Page Down	Right one page

Table 28–7: Keyboard Shortcuts for Selecting Data in a Datasheet
Open table as spreadsheet

Keys	Selecting Action
Tab	Next field
Shift+Tab	Previous field
Home	First field, current record
End	Last field, current record
Up Arrow	Current field, previous record
Down Arrow	Current field, next record
Ctrl+Up Arrow	Current field, first record
Ctrl+Down Arrow	Current field, last record
Ctrl+Home	First field, first record
Ctrl+End	Last field, last record
Alt+F5	Record Number box
Ctrl+Spacebar	The current column
Shift+Spacebar	The current record
F2	When in a field, toggles between selecting all data in the field and single-character edit mode

Working with Subdatasheets

Microsoft Access 2000 introduced a new feature that lets you display information from multiple related tables in a single datasheet. In the design we developed for the Conrad Systems Contacts sample database, contacts can have multiple contact events and contact products. In some cases, it might be useful to open a query on contacts and be able to see either related events or products in the same datasheet window.

You might have noticed the little plus-sign indicators in the datasheet for qryContactsDatasheet in Figure 28–31. Click the plus sign next to the second row to open the Contact Events subdatasheet as shown in Figure 28–33.

Figure 28–33: Click the plus sign to view the contact event details for the second contact in a subdatasheet.

A subdatasheet doesn’t appear automatically in a query, even if you’ve defined subdatasheet properties for your table as described in Chapter 27. We had to open the property sheet for the query in Design view and specify the subdatasheet you see. Figure 28–34 shows the properties we set. You can find more details about setting these properties in Chapter 27.

Figure 28–34: The property sheet for the qryContactsDatasheet query displays the subdatasheet properties.

You can click the plus sign next to each order row to see the contact event detail information for that contact. If you want to expand or collapse all the subdatasheets, click More in the Records group on the Home tab, click Subdatasheet, and then click the option you want as shown in Figure 28–35.

Figure 28–35: The Subdatasheet menu allows you to easily expand all subdatasheets, collapse all subdatasheets, or remove the currently displayed subdatasheet.

The information from the related tblContactEvents table is interesting, but what if you want to see the products the contact has purchased instead? To do this, while in Datasheet view, click More on the Home tab, click Subdatasheet, and then click Subdatasheet to see the dialog box shown in Figure 28–36.

Figure 28–36: You can choose a different table to display other related information in a subdatasheet from the Insert Subdatasheet dialog box.

We built a query in the sample database that displays the related company and product information for a contact. Click the Queries or Both tab and select qxmplCompanyContactProduct to define the new subdatasheet. Click OK to close the Insert Subdatasheet dialog box.

When you return to the qryContactsDatasheet window, click More on the Home tab, click Subdatasheet, and then click Expand All. You will now see information about each product ordered as shown in Figure 28–37. Note that you can also entirely remove a subdatasheet by clicking Remove on the menu shown in Figure 28–35. Close the query when you are finished.

Figure 28–37: You can review all product information for a contact from the subdatasheet by expanding it.

In the next section, you’ll learn more about editing data in Datasheet view. You can use these editing techniques with the main datasheet as well as with any expanded subdatasheet.

Caution 

When you close qryContactsDatasheet after modifying the subdatasheet as explained in this section, Access will prompt you to ask if you want to save your changes. You should click No to retain the original subdatasheet on tblContactEvents that we defined so that the remaining examples in this chapter make sense.

Changing Data

Not only can you view and format data in a datasheet, you can also insert new records, change data, and delete records.

Understanding Record Indicators

You might have noticed as you moved around in the datasheet that icons occasionally appeared on the row selector at the far left of each row. (See Figure 28–31.) These record indicators and their meanings follow. Note also that Access 2007 highlights the current row.

The pencil icon indicates that you are making or have made a change to one or more entries in this row. Access 2007 saves the changes when you move to another row. Before moving to a new row, you can press Esc once to undo the change to the current value, or press Esc twice to undo all changes in the row. If you’re updating a database that is shared with other users through a network, Access locks this record when you save the change so that no one else can update it until you’re finished. If someone else has the record locked, Access shows you a warning dialog box when you try to save the row. You can wait a few seconds and try to save again.

The asterisk icon indicates a blank row at the end of the table that you can use to create a new record.

Adding a New Record

As you build your application, you might find it useful to place some data in your tables so that you can test the forms and reports that you design. You might also find it faster sometimes to add data directly to your tables by using Datasheet view rather than by opening a form. If your table is empty when you open the table or a query on the table in Datasheet view, Access 2007 shows a single blank highlighted row with dimmed rows beneath. If you have data in your table, Access shows a blank row beneath the last record as well as dimmed rows below the blank row. You can jump to the blank row to begin adding a new record either by clicking the Go To command on the Home tab and then clicking New Record, by clicking the New button in the Records group on the Home tab, or by pressing Ctrl+plus sign. Access places the insertion point in the first column when you start a new record. As soon as you begin typing, Access changes the record indicator to the pencil icon to show that updates are in progress. Press the Tab key to move to the next column.

If the data you enter in a column violates a field validation rule, Access 2007 notifies you as soon as you attempt to leave the column. You must provide a correct value before you can move to another column. Press Esc or click the Undo button on the Quick Access Toolbar to remove your changes in the current field.

Press Shift+Enter at any place in the record or press Tab in the last column in the record to commit your new record to the database. You can also click the Save command in the Records group on the Home tab. If the changes in your record violate the validation rule for the table, Access warns you when you try to save the record. You must correct the problem before you can save your changes. If you want to cancel the record, press Esc twice or click the Undo button on the Quick Access Toolbar until the button appears dimmed. (The first Undo removes the edit from the current field, and clicking Undo again removes any previous edit in other fields until you have removed them all.)

Access 2007 provides several keyboard shortcuts to assist you as you enter new data, as shown in Table 28–8.

Table 28–8: Keyboard Shortcuts for Entering Data in a Datasheet
Open table as spreadsheet

Keys	Data Action
Ctrl+semicolon (;)	Enters the current date
Ctrl+colon (:)	Enters the current time
Ctrl+Alt+Spacebar	Enters the default value for the field
Ctrl+single quotation mark (') or Ctrl+double quotation mark (")	Enters the value from the same field in the previous record
Ctrl+Enter	Inserts a carriage return in a memo or text field
Ctrl+plus sign (+)	Moves to the new record row
Ctrl+minus sign (−)	Deletes the current record

Inside Out-Setting Keyboard Options 

You can set options that affect how you move around in datasheets and forms. Click the Microsoft Office Button, click Access Options, and click the Advanced category to see the options shown here.

You can change the way the Enter key works by selecting an option under Move After Enter. Select Don’t Move to stay in the current field when you press Enter. When you select Next Field (the default), pressing Enter moves you to the next field or the next row if you’re on the last field. Select Next Record to save your changes and move to the next row when you press Enter.

You can change which part of the data of the field is selected when you move into a field by selecting an option under Behavior Entering Field. Choose Select Entire Field (the default), to highlight all data in the field. Select Go To Start Of Field to place an insertion point before the first character, and select Go To End Of Field to place the insertion point after the last character.

Under Arrow Key Behavior select Next Field (the default) if you want to move from field to field when you press the Right Arrow or Left Arrow key. Select Next Character to change to the insertion point and move one character at a time when you press the Right Arrow or Left Arrow key. You can select the Cursor Stops At First/Last Field check box if you don’t want pressing the arrow keys to move you off the current row.

We personally prefer to set the Move After Enter option to Don’t Move and the Arrow Key Behavior option to Next Character. We use the Tab key to move from field to field, and we don’t want to accidentally save the record when we press Enter. We leave Behavior Entering Field at the default setting of Select Entire Field so that the entire text is selected, but setting Arrow Key Behavior to Next Character allows us to press the arrow keys to shift to single-character edit mode and move in the field.

Selecting and Changing Data

When you have data in a table, you can easily change the data by editing it in Datasheet view. You must select data before you can change it, and you can do this in several ways.

• In the cell containing the data you want to change, click just to the left of the first character you want to change (or to the right of the last character), and then drag the insertion point to select all the characters you want to change.

• Double-click any word in a cell to select the entire word.

• Click at the left edge of a cell in the grid (that is, where the mouse pointer turns into a large white cross). Access selects the entire contents of the cell.

Any data you type replaces the old, selected data. In Figure 28–38, we have moved to the left edge of the First Name field, and Access has shown us the white cross mentioned in the last bullet. We can click to select the entire contents of the field. In Figure 28–39, we have changed the value to Mike, but haven’t yet saved the row. (You can see the pencil icon indicating that a change is pending.) Access also selects the entire entry if you tab to the cell in the datasheet grid (unless you have changed the keyboard options as noted earlier). If you want to change only part of the data (for example, to correct the spelling of a street name in an address field), you can shift to single-character mode by pressing F2 or by clicking the location at which you want to start your change. Use the Backspace key to erase characters to the left of the insertion point and use the Delete key to remove characters to the right of the insertion point. Hold down the Shift key and press the Right Arrow or Left Arrow key to select multiple characters to replace. You can press F2 again to select the entire cell. A useful keyboard shortcut for changing data is to press Ctrl+Alt+Spacebar to restore the data in the current field to the default value specified in the table definition.

Figure 28–38: You can select the old data by clicking the left side of the column.

Figure 28–39: You can then replace the old data with new data by typing the new information.

Replacing Data

What if you need to make the same change in more than one record? Access 2007 provides a way to do this quickly and easily. Select any cell in the column whose values you want to change (the first row if you want to start at the beginning of the table), and then click the Replace command in the Find group on the Home tab or press Ctrl+F to see the dialog box shown in Figure 28–40. Suppose, for example, that you suspect that the city name Easton is misspelled as Eaton in multiple rows. (All the city names are spelled correctly in the sample table.) To fix this using Replace, select the Work City field in any row of qryContactsDatasheet, click the Replace command, type Eaton in the Find What text box, and then type Easton in the Replace With text box, as shown in Figure 28–40. Click the Find Next button to search for the next occurrence of the text you’ve typed in the Find What text box. Click the Replace button to change data selectively, or click the Replace All button to change all the entries that match the Find What text. Note that you can select options to look in all fields or only the current field; to select an entry only if the Find What text matches the entire entry in the field; to search All, Up, or Down; to exactly match the case for text searches (because searches in Access are normally case-insensitive); and to search based on the formatted contents (most useful when updating date/time fields).

Figure 28–40: The Find And Replace dialog box allows you to quickly replace data in more than one record.

Copying and Pasting Data

You can copy or cut any selected data to the Clipboard and paste this data into another field or record. To copy data in a field, tab to the cell or click at the left edge of the cell in the datasheet grid to select the data within it. Click the Copy command in the Clipboard group on the Home tab or press Ctrl+C. To delete (cut) the data you have selected and place a copy on the Clipboard, click the Cut command in the Clipboard group on the Home tab or press Ctrl+X. To paste the data in another location, move the insertion point to the new location, optionally select the data you want to replace, and click the Paste command in the Clipboard group on the Home tab or press Ctrl+V. If the insertion point is at the paste location (you haven’t selected any data in the field), Access inserts the Clipboard data.

Inside Out-Using the Office Clipboard 

If you select and copy to the Clipboard several items of text data, Access 2007 shows you the Office Clipboard task pane. Unlike the Windows Clipboard, this facility allows you to copy several separate items, and then select any one of them later to paste into other fields or documents. You might find this feature useful when you want to copy the contents of several fields from one record to another. You can, for example, copy a City field and then copy a State field while in one record and then later individually paste the values into another row. If you don’t see the Office Clipboard, you can open it by clicking the Dialog Box Launcher button to the right of the word Clipboard in the Clipboard group of the Home tab. The Office Clipboard task pane will appear just to the left of the Navigation Pane.

To select an entire record to be copied or cut, click the row selector at the far left of the row. You can drag through the row selectors or press Shift+Up Arrow or Shift+Down Arrow to extend the selection to multiple rows. Click the Copy command or press Ctrl+C to copy the contents of multiple rows to the Clipboard. You can also click the Cut command or press Ctrl+X to delete the rows and copy them to the Clipboard.

You can open another table or query and paste the copied rows into that datasheet, or you can click Paste, then Paste Append in the Clipboard group on the Home tab to paste the rows at the end of the same datasheet. When you paste rows into another table, the rows you’re adding must satisfy the validation rules of the receiving table, and the primary key values (if any) must be unique. If any validation fails, Access shows you an error message and cancels the paste. You cannot paste copies of entire records into the same table if the table has a primary key other than the AutoNumber data type. (You’ll get a duplicate primary key value error if you try to do this.) When the primary key is AutoNumber, Access generates new primary key values for you.

The Cut command is handy for moving those records that you don’t want in an active table to a backup table. You can have both tables open (or queries on both tables open) in Datasheet view at the same time. Simply cut the rows you want to move, switch to the backup table window, and paste the cut rows by using the Paste Append command.

When you paste one row, Access inserts the data and leaves your insertion point on the new record but doesn’t save it. You can always click Undo on the Quick Access Toolbar to avoid saving the single pasted record. When you paste multiple rows, Access must save them all as a group before allowing you to edit further. Access asks you to confirm the paste operation. (See Figure 28–41.) Click Yes to proceed, or click No if you decide to cancel the operation.

Figure 28–41: This message box asks whether you want to proceed with a paste operation.

Note 

You can’t change the physical sequence of rows in a relational database by cutting rows from one location and pasting them in another location. Access always pastes new rows at the end of the current display. If you close the datasheet after pasting in new rows and then open it again, Access displays the rows in sequence by the primary key you defined. If you want to see rows in some other sequence, see “Sorting and Searching for Data” on page 957.

Deleting Rows

To delete one or more rows, select the rows using the row selectors and then press the Delete key. For details about selecting multiple rows, see the previous discussion on copying and pasting data. You can also use Ctrl+minus sign to delete the current or selected row. When you delete rows, Access 2007 gives you a chance to change your mind if you made a mistake. (See Figure 28–42.) Click Yes in the message box to delete the rows, or click No to cancel the deletion. Because this database has referential integrity rules defined between tblContacts and several other tables, you won’t be able to delete contact records using qryContactsDatasheet. (Access shows you an error message telling you that related rows exist in other tables.) You would have to remove all related records from tblContactEvents, tblContactProducts, and tblCompanyContacts first.

Figure 28–42: This message box appears when you delete rows.

Caution 

After you click Yes in the confirmation message box, you cannot restore the deleted rows. You have to reenter them or copy them from a backup.

Working with Hyperlinks

Microsoft Access 97 (also known as version 8.0) introduced the Hyperlink data type. The Hyperlink data type lets you store a simple or complex link to a file or document outside your database. This link pointer can contain a Uniform Resource Locator (URL) that points to a location on the World Wide Web or on a local intranet. It can also use a Universal Naming Convention (UNC) file name to point to a file on a server on your local area network (LAN) or on your local computer drives. The link might point to a file that is a Web page or in a format that is supported by an ActiveX application on your computer.

A Hyperlink data type is actually a memo field that can contain a virtually unlimited number of characters. The link itself can have up to four parts.

• An optional descriptor that Access displays in the field when you’re not editing the link. The descriptor can start with any character other than a pound sign (#) and must have a pound sign as its ending delimiter. If you do not include the descriptor, you must start the link address with a pound sign.

• The link address expressed as either a URL (beginning with a recognized Internet protocol name such as http: or ftp:) or in UNC format (a file location expressed as \\server\share\path\file name). If you do not specify the optional descriptor field, Access displays the link address in the field. Terminate the link address with a pound sign (#).

• An optional subaddress that specifies a named location (such as a cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word document) within the file. Separate the subaddress from the ScreenTip with a pound sign (#). If you entered no subaddress, you still must enter the pound sign delimiter if you want to define a ScreenTip.

• An optional ScreenTip that appears when you move your mouse pointer over the hyperlink.

For example, a hyperlink containing all four items might look like the following:

 Viescas Download Page#http://www.vi escas.com/Info/li nks.htm#Downloads #Click to see the files you can download from Viescas.com

A hyperlink that contains a ScreenTip but no bookmark might look like this:

 Viescas.com Books#http://www.vi escas.com/Info/books.htm ##Click to see recommended books on Viescas.com

When you have a field defined using the Hyperlink data type, you work with it differently than with a standard text field. We included the Website field from tblContacts in the qryContactsDatasheet sample query (in  ContactsDataCopy.accdb). Open the query and scroll to the right, if necessary, so that you can see the Website field, and place your mouse pointer over one of the fields that contains data, as shown in Figure 28–43.

Figure 28–43: Place your mouse pointer over a hyperlink field in Datasheet view to show the hyperlink or the ScreenTip.

Activating a Hyperlink

Notice that the text in a hyperlink field is underlined and that the mouse pointer becomes a hand with a pointing finger when you move the pointer over the field. If you leave the pointer floating over the field for a moment, Access displays the ScreenTip. In the tblContacts table, the entries in the Website hyperlink field for some of the contacts contain pointers to Microsoft Web sites. When you click a link field, Access starts the 00 application that supports the link and passes the link address and subaddress to the application. If the link starts with an Internet protocol, Access starts your Web browser. In the case of the links in the tblContacts table, all are links to pages on the Microsoft Web site. If you click one of them, your browser should start and display the related Web page, as shown in Figure 28–44.

Figure 28–44: Here is the result of clicking a Web site link in the tblContacts table.

Inserting a New Hyperlink

To insert a hyperlink in an empty hyperlink field, tab to the field or click in it with your mouse. If you’re confident about the format of your link, you can type it, following the rules for the four parts noted earlier. If you’re not sure, right-click inside the hyperlink field, select Hyperlink from the shortcut menu that appears, and then select Edit Hyperlink from the submenu to see the dialog box shown in Figure 28–45. This dialog box helps you correctly construct the four parts of the hyperlink.

Figure 28–45: The dialog box used to insert a hyperlink shows you a list of files in the current folder.

The dialog box opens with Existing File Or Web Page selected in the Link To pane and Current Folder selected in the center pane, as shown in Figure 28–45. What you see in the list in the center pane depends on your current folder, the Web pages you’ve visited recently, and the files you’ve opened recently. You’ll see a Look In list where you can navigate to any drive or folder on your system. You can also click the Browse The Web button (the button with a globe and a spyglass) to open your Web browser to find a Web site you want, or the Browse For File button (an open folder icon) to open the Link To File dialog box to find the file you want. Click Existing File Or Web Page and click the Recent Files option to see a list of files that you recently opened.

We clicked the Browsed Pages option because we knew the hyperlink we wanted was a Web page that we had recently visited. You can enter the descriptor in the Text To Display box at the top. We clicked the ScreenTip button to open the Set Hyperlink ScreenTip dialog box you see in Figure 28–46. You can type the document or Web site address directly into the Address box. (Yes, that’s Jeff’s real Web site address!)

Figure 28–46: You can choose a Web site address from a list of recently visited Web sites.

The E-Mail Address button in the left pane lets you enter an e-mail address or choose from a list of recently used addresses. This generates a mailto: hyperlink that will invoke your e-mail program and start a new e-mail to the address you enter. You can also optionally specify a subject for the new e-mail by adding a question mark after the e-mail address and entering what you want to appear on the subject line.

Click OK to save your link in the field in the datasheet.

Editing an Existing Hyperlink

Getting into a hyperlink field to change the value of the link is a bit tricky. You can’t simply click in a hyperlink field because that activates the link. What you can do is click in the field before the hyperlink and use the Tab key to move to the link field. Then press F2 to shift to character edit mode to edit the text string that defines the link. Figure 28–47 shows you a hyperlink field after following this procedure. You can use the arrow keys to move around in the text string to change one or more parts. In many cases, you might want to add an optional descriptor at the beginning of the link text, as shown in the figure.

Figure 28–47: You can edit the text that defines a hyperlink directly in a datasheet.

The most comprehensive way to work with a hyperlink field is to right-click a link field to open a shortcut menu. Clicking Hyperlink on this menu displays a submenu with a number of options. You can edit the hyperlink (which opens the dialog box shown in Figure 28–45), open the link document, copy the link to the Clipboard, add the link to your list of favorites, change the text displayed in the field, or remove the hyperlink.

Sorting and Searching for Data

When you open a table in Datasheet view, Access 2007 displays the rows sorted in sequence by the primary key you defined for the table. If you didn’t define a primary key, you’ll see the rows in the sequence in which you entered them in the table. If you want to see the rows in a different sequence or search for specific data, Access provides you with tools to do that. When you open a query in Datasheet view (such as the qryContactsDatasheet sample query we’re using in this chapter), you’ll see the rows in the order determined by sort specifications in the query. If you haven’t specified sorting information, you’ll see the data in the same sequence as you would if you opened the table or query in Datasheet view.

Sorting Data

Access 2007 provides several ways to sort data in Datasheet view. As you might have noticed, two handy Ribbon commands allow you to quickly sort the rows in a query or table datasheet in ascending or descending order. To see how this works, open the qryContactsDatasheet query, click anywhere in the Birth Date column, and click the Descending command in the Sort & Filter group on the Home tab. Access sorts the display to show you the rows ordered alphabetically by Birth Date, as shown in Figure 28–48.

Figure 28–48: You can sort contacts by birth date by using the sort buttons on the Ribbon.

You can click the Ascending button to sort the rows in ascending order or click the Clear All Sorts button to return to the original data sequence. But before you change the sort or clear the sort, suppose you want to see contacts sorted by state or province ascending and then by birth date descending. You already have the data sorted by birth date, so click anywhere in the State/Province column and click the Ascending button to see the result you want as shown in Figure 28–49.

Figure 28–49: After applying the second sort, the records are now sorted by state or province ascending and then by birth date descending within state or province.

Inside Out-Applying Multiple Sorts in Reverse Order 

Notice that to sort by state or province and then birth date within state or province, you must first sort birth date and then sort state or province. We think that’s backwards, but that’s the way it works. If you had applied a sort on state or province first and then sorted birth date, you would have seen all the records in date order with any records having the same date subsequently sorted by state or province. If you want to sort on multiple fields, remember to apply the innermost sort first and then work your way outward.

Another way to sort more than one field is to use the Advanced Filter/Sort feature. Let’s assume that you want to sort by State/Province, then by City within State/Province, and then by Last Name. Here’s how to do it:

1. Click the Advanced button in the Sort & Filter group on the Home tab, and then click Advanced Filter/Sort. You’ll see the Advanced Filter Design window (shown in Figure 28–50) with a list of fields in the qryContactsDatasheet query shown in the top part of the window.

   
   Figure 28–50: Select the fields you want to sort in the Advanced Filter Design window.

2. If you didn’t click the Clear All Sorts button before opening this window, you should see the sorts you previously defined directly in Datasheet view on the WorkStateOrProvince and BirthDate fields. If so, click the bar above the BirthDate field to select it and then press the Delete key to remove the field.

3. Because you recently sorted by State/Province, the Advanced Filter Design window will show this field already added to the filter grid. If you skipped the sort step in Figure 28–48 or closed and reopened the datasheet without saving the sort, open the field list in the first column by clicking the arrow or by pressing Alt+Down Arrow on the keyboard. Select the WorkStateOrProvince field in the 00 list. You can also place the WorkStateOrProvince field in the first column by finding WorkStateOrProvince in the list of fields in the top part of the window and dragging it into the Field row in the first column of the filter grid.

4. Click in the Sort row, immediately below the WorkStateOrProvince field, and select Ascending from the drop-down list.

5. Add the WorkCity and LastName fields to the next two columns, and select Ascending in the Sort row for both.

6. Click the Toggle Filter button in the Sort & Filter group of the Home tab on the Ribbon to see the result shown in Figure 28–51.

Figure 28–51: After defining your sorts and clicking the Toggle Filter button, you can see the results of your sorting contact records by state or province, city, and then last name.

Note 

If you compare Figure 28–49 with Figure 28–51, it looks like the records in Figure 28–49 were already sorted by city name within state. You might be tempted to leave out the sort on city in this exercise, but if you do that, you will not see the city names maintained in the same order. Remember, if you want data presented in a certain sequence, you must ask for it that way!

Close the qryContactsDatasheet window and click No when asked if you want to save design changes. We’ll explore using the other options in the Sort & Filter group in the next sections.

Searching For and Filtering Data

If you want to look for data anywhere in your table, Access 2007 provides several powerful searching and filtering capabilities.

Using Find   To begin this exercise, open the qryContactsDatasheet query in Datasheet view again. To perform a simple search on a single field, select that field, and then open the Find And Replace dialog box (shown in Figure 28–52) by clicking the Find command in the Find group on the Home tab or by pressing Ctrl+F.

Figure 28–52: You can use the Find And Replace dialog box to search for data.

In the Find What text box, type the data that you want Access to find. You can include wildcard characters similar to those of the LIKE comparison operator. See “Defining Simple Field Validation Rules” on page 858 to perform a generic search. Use an asterisk (*) to indicate a string of unknown characters of any length (zero or more characters), and use a question mark (?) to indicate exactly one unknown character or a space. For example, *AB??DE* matches Aberdeen and Tab idea but not Lab department.

By default, Access searches the field that your insertion point was in before you opened the Find And Replace dialog box. To search the entire table, select the table or query name from the Look In list. By default, Access searches all records from the top of the recordset unless you change the Search list to search down or up from the current record position. Select the Match Case check box if you want to find text that exactly matches the uppercase and lowercase letters you typed. By default, Access is caseinsensitive unless you select this check box.

The Search Fields As Formatted check box appears dimmed unless you select a field that has a format or input mask applied. You can select this check box if you need to search the data as it is displayed rather than as it is stored by Access. Although searching this way is slower, you probably should select this check box if you are searching a date/time field. For example, if you’re searching a date field for dates in january, you can specify *-Jan-* if the field is formatted as Medium Date and you select the Search Fields As Formatted check box. You might also want to select this check box when searching a Yes/No field for Yes because any value except 0 is a valid indicator of Yes.

Click Find Next to start searching from the current record. Each time you click Find Next again, Access moves to the next value it finds, and loops to the top of the recordset to continue the search if you started in the middle. After you establish search criteria and you close the Find And Replace dialog box, you can press Shift+F4 to execute the search from the current record without having to open the dialog box again.

Filtering by Selection   If you want to see all the rows in your table that contain a value that matches one in a row in the datasheet grid, you can use the Selection command in the Sort & Filter group on the Home tab. Select a complete value in a field to see only rows that have data in that column that completely matches. Figure 28–53 shows the value PA selected in the State/Province column and the result after clicking the Selection button in the Sort & Filter group of the Home tab and clicking Equals “PA”. If the filtering data you need is in several contiguous columns, click the first column, hold down the Shift key, and click the last column to select all the data; click the Selection button; and then click a filter option to see only rows that match the data in all the columns you selected.

Figure 28–53: Here is the list of contacts in Pennsylvania, compiled using the Selection filter option.

Alternatively, if you want to see all the rows in your table that contain a part of a value that matches one in a row in the datasheet grid, you can select the characters that you want to match and use Selection. For example, to see all contacts that have the characters ing in their work city name, find a contact that has ing in the Work City field and select those characters. Click the Selection button in the Sort & Filter group of the Home tab, and then click Contains “ing”. When the search is completed you should see only the three contacts who work in the cities named Pingree Grove and Flushing. To remove a filter, click the Toggle Filter button in the Sort & Filter group, or click Advanced in the Sort & Filter group and then click Clear All Filters.

Note 

You can open any subdatasheet defined for the query and apply a filter there. If you apply a filter to a subdatasheet, you will filter all the subdatasheets that are open.

You can also add a filter to a filter. For example, if you want to see all contacts who live in Youngsville in Pennsylvania, find the value PA in the State/Province column, select it, click the Selection button in the Sort & Filter group of the Home tab, and then click Equals “PA”. In the filtered list, find a row containing the word Youngsville in the Work City field, select the word, click the Selection button again, and click Equals “Youngsville”. Access displays a small filter icon that looks like a funnel in the upperright corner of each column that has a filter applied, as shown in Figure 28–54. If you rest your mouse on one of these column filter icons, Access displays a ScreenTip telling you what filter has been applied to that particular column. To remove all your filters, click the Toggle Filter button or click Advanced in the Sort & Filter group of the Home tab and click Clear All Filters.

Figure 28–54: Access displays a ScreenTip on the filter icon in the column header to show you what filter is applied.

Using the Filter Window   To further assist you with filtering rows, Access 2007 provides a Filter window with predefined filter selections for various data types. Suppose you want to quickly filter the rows for contacts who have birthdays in the month of December. Click inside the Birth Date column in any row and then click the Filter button in the Sort & Filter group of the Home tab, and Access opens the Filter window for this field shown in Figure 28–55.

Figure 28–55: The Filter window for date/time fields displays filter criteria based on the dates entered in the field.

The Ascending and Descending buttons, discussed previously, are the first two options in the Filter window. (For a date/time field, Access shows you Sort Oldest To Newest and Sort Newest To Oldest. For a text field, Access shows you Sort A To Z and Sort Z To A, and for a numeric field, Access shows you Sort Smallest To Largest and Sort Largest to Smallest.) The third option, Clear Filter From Birth Date, removes all filters applied to the Birth Date field. The fourth option is Date Filters, which displays several submenus to the right that allow you to filter for specific date criteria. (For text fields, this option presents a list of text filters. For number fields, Access displays a list of the available numeric values.)

Beneath the Date Filters option is a list. The first two options in this list are the same for all data types. Select All selects all the options presented in the list. Blanks causes Access to search the field for any rows with no value entered-a Null value or an empty string. Beneath Select All and Blanks are every unique value entered into the Birth Date field for the current datasheet. If you select only one of these options, Access filters the rows that exactly match the value you choose.

In our example, to find all contacts who have a birthday in the month of December, click Date Filters, click All Dates In Period, and then you can filter the rows by an individual quarter or by a specific month. Click December and Access filters the rows to display only contacts who have birthdays in December, as shown in Figure 28–56.

Figure 28–56: Date Filters presents built-in date filters for periods and months.

The result of this filter should return the four contacts who have birthdays in December as shown in Figure 28–57. Click the Toggle Filter button in the Sort & Filter group of the Home tab to remove the filter.

Figure 28–57: Four contacts in the table have birthdays in the month of December.

Using Filter By Form Applying a filter using Selection is great for searching for rows that match all of several criteria (Last Name like “*son*" and State/Province equals “OR”), but what if you want to see rows that meet any of several criteria (Last Name like “*son” and State/Province equals “OR” or State/Province equals “PA”)? You can use Filter By Form to easily build the criteria for this type of search.

When you click the Advanced button in the Sort & Filter group of the Home tab and click Filter By Form, Access 2007 shows you a Filter By Form example that looks like your datasheet but contains no data. If you have no filtering criteria previously defined, Access shows you the Look For tab and one Or tab at the bottom of the window. Move to each column in which you want to define criteria and either select a value from the drop-down list or type search criteria, as shown in Figure 28–58. Notice that each dropdown list shows you all the unique values available for each field, so it’s easy to pick values to perform an exact comparison. You can also enter criteria, much the way that you did to create validation rules in Chapter 27. For example, you can enter Like “*son*” in the Last Name field to search for the letters son anywhere in the name. You can use criteria such as >#01 JAN 1963# in the Birth Date date/time field to find rows for contacts born after that date. You can enter multiple criteria on one line, but all the criteria you enter on a single line must be true for a particular row to be selected.

Figure 28–58: Use Filter By Form to search for one of several states.

Inside Out-Limiting the Returned Records 

When your table or query returns tens of thousands of rows, fetching the values for each list in Filter By Form can take a long time. You can specify a limit by clicking the Microsoft Office Button and clicking Access Options. Select the Current Database category in the Access Options dialog box, and then scroll down to Filter Lookup Options For <name of your database>. In the Don’t Display Lists Where More Than This Number Of Records Read option, you can specify a value for display lists to limit the number of discrete values returned. The default value is 1,000.

If you want to see rows that contain any of several values in a particular column (for example, rows from several states), enter the first value in the appropriate column, and then click the Or tab at the bottom of the window to enter an additional criterion. In this example, “OR” was entered in the State/Province column on the Look For tab and “PA” on the first Or tab; you can see “PA” being selected for the first Or tab in Figure 28–58.

Each tab also specifies Like “*son*" for the last name. (As you define additional criteria, Access makes additional Or tabs available at the bottom of the window.) Figure 28–59 shows the result of applying these criteria when you click the Toggle Filter button in the Sort & Filter group of the Home tab.

Figure 28–59: The contacts with names containing son in the states of OR and PA.

You can actually define very complex filtering criteria using expressions and the Or tabs in the Filter By Form window. If you look at the Filter By Form window, you can see that Access builds all your criteria in a design grid that looks similar to a Query window in Design view. In fact, filters and sorts use the query capabilities of Access to accomplish the result you want, so in Datasheet view you can use all the same filtering capabilities you’ll find for queries.

Inside Out-Saving and Reusing Your Filters 

Access 2007 always remembers the last filtering and sorting criteria you defined for a datasheet. The next time you open the datasheet, click the Advanced button in the Sort & Filter group on the Home tab and click Filter By Form or Advanced to apply the last filter you created (as long as you replied Yes to the prompt to save formatting changes when you last closed the datasheet). If you want to save a particular filter/sort definition, click the Advanced button in the Sort & Filter group on the Home tab, click Save As Query, and give your filter a name. The next time you open the table, return to the Advanced button, and then click Load From Query to find the filter you previously saved.