When you’re developing an application, you might need to work in table or query Data-sheet 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 2010 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 ContactsData-Copy.accdb from your sample files folder. In that database, you’ll find a query named qryContactsDatasheet, which 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.
Open the qryContactsDatasheet query in the ContactsDataCopy.accdb database. You should see a result similar to Figure 9-36. 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 9-36. 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 9-37. 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), this number might be less than the total number of records in the table or query.
Figure 9-37. 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 9-6 lists the keyboard shortcuts for scrolling in a datasheet. Table 9-7 lists the keyboard shortcuts for selecting data in a datasheet.
Table 9-6. Keyboard Shortcuts for Scrolling in a Datasheet
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 9-7. Keyboard Shortcuts for Selecting Data in a Datasheet
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 |
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 9-36. Click the plus sign next to the second row to open the Contact Events Subdatasheet, as shown in Figure 9-38.
Figure 9-38. 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 4. We had to open the property sheet for the query in Design view and specify the subdatasheet you see. Figure 9-39 shows the properties we set. Note that you need to click the top part of the query design window around the tables to see the properties for the query in the Property Sheet window. You can find more details about setting these properties in Chapter 4 and Chapter 10.
Figure 9-39. 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 on the menu, and then click the option you want as shown in Figure 9-40.
Figure 9-40. 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 on the menu, and then click Subdatasheet to see the dialog box shown in Figure 9-41.
Figure 9-41. 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 tab or the 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 on the menu, and click the Expand All option on the submenu. You will now see information about each product ordered, as shown in Figure 9-42. Note that you can also entirely remove a subdatasheet by clicking Remove on the menu, shown in Figure 9-40. Close the query when you are finished.
Figure 9-42. 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 qryContactDatasheet 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.
Not only can you view and format data in a datasheet, you can also insert new records, change data, and delete records.
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 9-36.) These record indicators and their meanings follow. Note also that Access 2010 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 2010 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.
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 2010 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 on the submenu, 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 2010 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 2010 provides several keyboard shortcuts to assist you as you enter new data, as shown in Table 9-8.
Table 9-8. Keyboard Shortcuts for Entering Data in a Datasheet
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 File tab on the Backstage view, click Options, and click the Client Settings 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.
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 9-43, 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 9-44, 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.
What if you need to make the same change in more than one record? Access 2010 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 9-45. 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 9-45. Click Find Next to search for the next occurrence of the text you’ve typed in the Find What text box. Click Replace to change data selectively, or click Replace All 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).
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, 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 2010 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. To select all records in the datasheet, press Ctrl+A. 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 use the Paste Append command on the submenu beneath the Paste command 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 with the same field structure, 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 9-46.) Click Yes to proceed, or click No if you decide to cancel the 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.
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 2010 gives you a chance to change your mind if you made a mistake. (See Figure 9-47.) 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.
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.viescas.com/Info/links.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.viescas.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 9-48.
Figure 9-48. Place your mouse pointer over a hyperlink field in Datasheet view to show the hyperlink or the ScreenTip.
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 websites. When you click a link field, Access starts the 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 website. If you click one of them, your browser should start and display the related web page, as shown in Figure 9-49.
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 submenu that appears, and then select Edit Hyperlink from the second submenu to see the dialog box shown in Figure 9-50. This dialog box helps you correctly construct the four parts of the hyperlink.
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 9-50. 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 website 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.
Figure 9-50. The dialog box used to insert a hyperlink shows you a list of files in the current folder.
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 ScreenTip to open the Set Hyperlink ScreenTip dialog box you see in Figure 9-51. You can type the document or website address directly into the Address box. (Yes, that’s Jeff’s real website address!)
The E-Mail Address button in the left pane lets you enter an email address or choose from a list of recently used addresses. This generates a mailto: hyperlink that will invoke your email program and start a new email to the address you enter. You can also specify a subject for the new email by adding a question mark after the email address and entering what you want to appear on the subject line.
The Hyperlink Builder button in the left pane helps you build a hyperlink that includes parameters. You’ll learn more about using parameters in hyperlinks in Chapter 12.
Click OK to save your link in the field in the datasheet.
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 9-52 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.
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 9-50), 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.
When you open a table in Datasheet view, Access 2010 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 qryContactDatasheet 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.
Access 2010 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 9-53.
You can click Ascending to sort the rows in ascending order or click Remove Sort 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 Ascending to see the result you want, as shown in Figure 9-54.
Figure 9-54. After applying the second sort, the records are now sorted by state or province ascending and then by birth date descending within state.
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:
Click the Advanced button in the Sort & Filter group on the Home tab, and then click Advanced Filter/Sort on the submenu. You’ll see the Advanced Filter Design window (shown in Figure 9-55) with a list of fields in the qryContactsDatasheet query shown in the top part of the window.
If you didn’t click Remove Sort 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.
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 9-53 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 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.
Click in the Sort row, immediately below the WorkStateOrProvince field, and select Ascending from the drop-down list.
Add the WorkCity and LastName fields to the next two columns, and select Ascending in the Sort row for both.
Click the Toggle Filter button in the Sort & Filter group of the Home tab on the ribbon to see the result shown in Figure 9-56.
Note
If you compare Figure 9-54 with Figure 9-56, it looks like the records in Figure 9-54 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.
If you want to look for data anywhere in your table, Access 2010 provides several powerful searching and filtering capabilities.
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 9-57) by clicking the Find command in the Find group on the Home tab or by pressing Ctrl+F.
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, 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 Current Document 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 case-insensitive 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.
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 9-58 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” on the submenu.
Figure 9-58. 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 WorkCity field and select those characters. Click the Selection button in the Sort & Filter group of the Home tab, and then select Contains “ing” from the submenu. 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 click Clear All Filters on the submenu.
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” on the submenu. In the filtered list, find a row containing the word Youngsville in the Work City field, select the word, click Selection again, and click Equals “Youngsville” on the submenu. Access displays a small filter icon that looks like a funnel in the upper-right corner of each column that has a filter applied, as shown in Figure 9-59. 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 Toggle Filter, or click Advanced in the Sort & Filter group of the Home tab and click Clear All Filters on the submenu.
To further assist you with filtering rows, Access 2010 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, as shown in Figure 9-60.
Figure 9-60. 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, and a submenu appears to the right. Select All Dates In Period, and a second submenu appears to the right of the first, which allows you to 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 9-61.
The result of this filter should return the four contacts who have birthdays in December, as shown in Figure 9-62. Click the Toggle Filter button in the Sort & Filter group of the Home tab to remove the filter.
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 on the submenu, Access 2010 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 9-63. Notice that each drop-down 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 4. 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.
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 File tab on the Backstage view and clicking 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 When More Than This Number Of Records Is 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 9-63.
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 9-64 shows the result of applying these criteria when you click the Toggle Filter button in the Sort & Filter group of the Home tab.
Inside Out: Saving and Reusing Your Filters
Access 2010 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 (so 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 on the submenu, and give your filter a name. The next time you open the table, return to the Advanced button and then click Load From Query on the submenu to find the filter you previously saved.
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.
In Chapter 10, we’ll explore creating more complex queries—including creating queries from multiple tables or queries, calculating totals, and designing PivotTable and PivotChart views. You’ll also learn how to create queries that will publish to Access Services.