Finding records with the data form
You can use the Criteria button in the data form to find the records in your data list that you need to edit or delete (as described in the next sections). When you click the Criteria button, Excel clears all the field text boxes so that you can enter the criteria to search for. For example, say that you need to edit Jake Mendore's profit-sharing status. You don't have her paperwork in front of you, so you can't look up her employee number. You do know, however, that he works in the New York office, and although you don't remember exactly how he spells his last name, you do know that it begins with a M instead of a K.
To locate her record, you can at least narrow the search to all the records where the Location field contains New York and the employee's Last Name begins with the letter M. To do this, open the data form for the Employee data list, click the Criteria button, and then enter the following text in the Last Name field:
M*
Then, in the Location field you enter
New York
When entering the criteria for locating matching records in the data form, you can use the question mark (?) and asterisk (*) wildcard characters just as you do when using the Excel Find feature to locate cells with particular entries.
Then click the Find Next button or press the Enter key, and Excel locates the first record in the data list where the last name begins with the letter M and the location is New York. This is Joseph's Rune record.
To locate the next record that matches your criteria, click the Find Next button or press Enter, which brings you to Jake Mendora's record. Having located Jake's record, you can then change her profit-sharing status by selecting the Profit Sharing text box and replacing No with Yes. To insert the editing change that you make in the data form into the data list itself, close the data form's dialog box by clicking the Close button.
When using the Criteria button in the data form to find records, you can use the following logical operators when entering search criteria in fields that use numbers or dates:
- Equal to (=): Finds records the same as the text, value, or date you enter.
- Greater than (>): Finds records after the text characters (in the alphabet) or the date, or larger than the value you enter.
- Greater than or equal to (>=): Finds records the same as the text characters, date, or value you enter or after the characters (in the alphabet), after the date, or larger than the value.
- Less than (<): Finds records before the text characters (in the alphabet) or date or smaller than the value you enter.
- Less than or equal to (<=): Finds records the same as the text characters, date, or value you enter or before the characters (in the alphabet) or the date, or smaller than the value.
- Not equal to (< >): Finds records not the same as the text, value, or date you enter.
For example, to find all the records where an employee's annual salary is $50,000, you can enter =50000 or simply 50000 in the Salary field text box. However, to find all the records for employees whose annual salaries are less than or equal to $35,000, you enter <=35000 in the Salary field text box. To find all the records for employees with salaries greater than $45,000, you would enter >45000 in the Salary field text box instead. If you wanted to find all the records where the employees are male and make more than $50,000, you would enter M in the Sex field text box and >50000 in the Salary field text box in the same Criteria data form.
When specifying search criteria that fit a number of records, you may have to click the Find Next or Find Prev buttons several times to locate the record you want to work with. If no record fits the search criteria you enter in the Criteria data form, the computer beeps at you when you click the Find Next or Find Prev button.
To change your search criteria, select the appropriate text box or boxes, delete the old search criteria, and then enter the new criteria. To switch back to the current record without using the search criteria you enter, click the Form button. (This button replaces the Criteria button as soon as you click the Criteria button.)