Search, replace and filter data : MS Access

To Find and Replace Information: Select Edit » Replace from the menu, or press Ctrl + H. Enter the text you want to search for in the Find What text box, enter the text you want to replace the word with in the Replace With text box. Click the Find Next button to move to the first occurrence of the text and click the Replace button to replace the text or click the Find Next button to move to the next occurrence of the text. Repeat if there is more than one occurrence that you want to replace or click Replace All to search for and replace every occurrence of text in the table.

Sorting Records

To Sort Records by One Field: Click anywhere in the column you want to use to sort the list, and click either the Sort Ascending button or Sort Descending button on the toolbar.

Filtering by Selection

To Filter by Selection: Find the field value on which you want to base the filter, right-click the field value, and select Filter by Selection from the shortcut menu, or click the field value, then click the Filter by Selection button on the toolbar.

Filter Excluding the Selection: Right-click the field value you want to exclude and select Filter Excluding Selection from the shortcut menu.

To Remove a Filter: Right-click the filtered table and select Remove Filter/Sort from the shortcut menu, or click the Remove Filter button on the toolbar.

Filtering by Form

To Filter by Form: Click the Filter by Form button on the toolbar, click the text box for the field you want to filter, click the drop-down arrow, and select the value you want to use to filter the records. Repeat this step for each additional field you want to use to specify additional filter criteriaif you want to use Or criteria, click the Or tab at the bottom of the screen to specify the additional filter criteria. Click the Apply Filter button on the toolbar.

Creating an Advanced Filter

To Create an Advanced Filter: Select Records » Filter » Advanced Filter/Sort from the menu, and then double-click each field you want to include from the field list, or drag the field from the field list onto the design grid. In the design grid, enter any desired search criteria for the field in the Criteria row. Click the Apply Filter button on the toolbar.

Adjusting Row Height and Column Width

To Adjust the Width of a Column: Drag the column header's right border to the left or right. You can also right-click the column header(s), select Column Width from the shortcut menu, and enter the column width, or you can select the column header(s), select Format » Column Width from the menu, and enter the column width.

To Adjust the Height of a Row: Drag the row header's bottom border up or down. You can also right-click the row header(s), select Row Height from the shortcut menu, and enter the row height or select the row header(s), select Format » Row Height from menu and enter the row height.

To Automatically Adjust the Width of a Column or Row: Double-click the right border of the column or bottom border of a row.

Rearranging Columns

To Move a Column: Click the field name of the column you want to move, then drag the selected column to its new location.

Changing Gridline and Cell Effects

To Change a Datasheet's Gridline Effects: Select Format » Datasheet from the menu, select the Cell Effect option you want, and click OK.

Freezing a Field

To Freeze a Column: Right-click the column field name you want to freeze and select Freeze Columns from the shortcut menu.

To Unfreeze a Column: Select Format » Unfreeze All Columns from the menu.

Hiding a Column

To Hide a Column: Right-click the column field name you want to hide and select Hide Columns from the shortcut menu.

To Unhide a Column: Select Format » Unhide Columns from the menu.

Changing the Datasheet Font

To Open the Font Dialog Box: Select Format » Font from the menu.

Quiz

Which of the following is NOT a command to find specific words or phrases in a database?
1. Click the Find button on the toolbar.
2. Select Edit » Find from the menu.
3. Click the Find button on the record navigation button area.
4. Press Ctrl + F.
The only way to find and replace information in Microsoft Access is with an Update Query. (True or False?)
Which of the following is NOT true? (Select all that apply.)
1. Filter by Selection finds all records that match a selected value.
2. Filter Excluding Selection finds all records that do not match a selected value.
3. Filter by Form lets you enter your filter criteria in a blank form and works well so long as you do not need to use multiple AND/OR criteria.
4. An Advanced Filter is similar to creating a simple select query.
The criteria BETWEEN 1/1/99 AND 12/31/99 would:
1. Display records between the dates 1/2/99 and 1/1/00.
2. Display records whose dates equaled 1/1/99 or 12/31/99.
3. Display records between the dates 1/1/99 and 12/31/99.
4. Do nothing - this criteria has not been entered using the proper syntax.
In an Advanced Filter, which of the following are ways you can add fields to the design grid? (Select all that apply.)
1. Select the field from the Add Field List on the toolbar.
2. Double-click the field in the field list.
3. Select Edit » Add Field from the menu, select the field from the list, and then click OK.
4. Drag and drop the field from the field list to the design grid.
The only way you can rearrange the order of fields in a datasheet is by reordering the fields in table Design View. (True or False?)
Which of the following statements is NOT true?
1. Bill Gates has more money than I do.
2. When you freeze a field it stays in the same place while you scroll around the rest of the datasheet.
3. You can temporarily hide a field or column if you want to reduce the amount of information that is displayed on the screen.
4. To hide a field, select the field and click the Hide Column button on the toolbar.
How do you freeze a column or field in Microsoft Access?
1. Click anywhere in the column and click the Freeze button on the toolbar.
2. Place an ice cube on the column.
3. Right-click the column and select Freeze Columns from the shortcut menu.
4. Click anywhere in the column and select Edit » Freeze Column from the menu.
How do you filter by selection?
1. Find and double-click the value on which you want to base the filter.
2. Find the value on which you want to base the filter, right-click the field value, and select Filter by Selection from the shortcut menu.
3. Find and select the value on which you want to base the filter and select Tools » Filter by Selection from the menu.
4. This feature is found in Microsoft Excel, not Access.
What is a fast way to adjust the width of a column?
1. Double-click the left side of the column heading.
2. Double-click the right side of the column heading.
3. Right-click the left side of the column heading.
4. Select Tools » Adjust Column Width from the menu.

Homework

Start Microsoft Access and open the Homework database.
Open the Customers table in Datasheet View.
Click anywhere in the LastName field and find any customers with the last name "Eller."
With the cursor in the FirstName field, use the Find and Replace command to replace every instance of the first name "John" with the first name "Jack."
Use the Sort command to sort the Customers table by the LastName field, in ascending order.
Use the Filter by Selection command to display only records from the state of Texas (TX). Hint: Right-click any TX value in the State field and select Filter by Selection from the shortcut menu.
Remove the filter.
Use the Filter by Form command to display only those records from "TX" or "MN."
Create an Advanced Filter to display records for customers from "TX" or "MN" and who were born before 1/1/1950.
Close Microsoft Access.

Quiz Answers

C. There are buttons to add and navigate records in the record navigation area, however, there isn't a Find button.
False. An Update Query can find and replace information in Microsoft Access (more about that later) but so can the Find and Replace command, which you can use by selecting Edit » Replace or pressing Ctrl + H.
C. Filter by Form is great for using multiple AND/OR criteria.
C. Displays records between the dates 1/1/99 and 12/31/99.
B and D. Both of these are ways to add fields to the design grid in an Advanced Filter.
False. You can rearrange the order of fields in a datasheet by simply selecting them and then dragging and dropping them to the new desired location.
D. To hide a field, simply right-click the column and select Hide Columns from the shortcut menu.
C. You can freeze a column by right-clicking the column and selecting Freeze Columns from the shortcut menu.
B. You can filter a selection by finding the value on which you want to base the filter, right-clicking the field value, and selecting Filter by Selection from the shortcut menu.
B. Double-clicking the right side of a column automatically adjusts its width.