Amal Nagm

Find and replace data in a table

Find and replace data

1. In your table, select the field (column) that you want to search. Note If you want to search the entire table, follow the alternate procedure in step 5.
   
2. On the Home tab, in the Find group, click Find. Keyboard shortcut Press CTRL+F.
   This figure shows the command:
   
   The Find and Replace dialog box appears.
   
3. To find data, in the Find and Replace dialog box, click the Find tab. To run a find-and-replace operation, click the Replace tab.
4. In the Find What box, type your search string. To replace data, enter a replacement string in the Replace With box. Note Do not enter wildcard characters in the Replace With box unless you want your records to contain them.
   
5. Optionally, use the Look In list to change the field that you want to search, or to search the entire table instead. By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
   -or-
   If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
   
6. Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
7. Make sure that the Search Fields As Formatted check box is selected, and then click Find Next.

Find and replace data in a form

Find data in a form

1. Open the form that you want to search in Form view or Layout view.
2. Select the control that contains the information that you want to search. Note If you want to search the entire table, follow the alternate procedure in step 5.
   
3. On the Home tab, in the Find group, click Find. –or–
   Press CTRL+F.
   The Find and Replace dialog box appears.
   
4. To find data, click the Find tab. To run a find-and-replace operation, click the Replace tab.
5. In the Find What box, type your search value. To replace data, type a replacement string in the Replace With box. Note Do not enter wildcard characters in the Replace With box unless you want your records to contain them.
   
6. Optionally, use the Look In list to change the field that you want to search, or to search the whole underlying table instead. By default, the Look In list contains the name of the control that you selected in step 1. If you want to search the table that underlines the form, select the name of the table from the list.
   -or-
   If you decide to select another control (the equivalent of selecting another table field), click the control on the form at any time. You do not need to close the dialog box.
   
7. Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
8. In the Search list, click All, and then click Find Next. To replace a string, click Replace. If you are sure that you have entered the correct replacement string, click Replace All, but keep in mind that you cannot undo a replace operation. If you make a mistake, you will have to repeat the find-and-replace operation, find the incorrect values, and replace them with the correct values.

Find data in a query result set

Create a select query

• Open the database that contains the records you want to find.
• On the Create tab, in the Other group, click Query Design. Access starts the query designer, the Design tab is displayed, and the Show Table dialog box appears.
  
• Select the table or tables that contain the records you want to find and click Add, and then click Close. The table or tables appear as one or more windows in the upper section of the query design grid, and the windows list all the fields in each table. The following figure shows the designer with a typical table:
  
  
• Double-click the fields that you want to find. The selected fields appear in the Field row in the lower section of the query designer. You can add one table field per column in the lower section.
  To add all the fields in a table quickly, double-click the asterisk (*) at the top of the list of table fields. The following figure shows the designer with all fields added.
  
  
• Optionally, you can enter one or more criteria in the Criteria row of the design grid. Doing so can reduce the number of records that the query returns and make it easier to find your data. The following table shows some example criteria and explains the effect they have on a query.
  

  Criteria	Effect
  > 234	Returns all numbers greater than 234. To find all numbers less than 234, use < 234.
  >= "Callahan"	Returns all records from Callahan through the end of the alphabet
  Between #2/2/2006# And #12/1/2006#	Returns dates from 2-Feb-06 through 1-Dec-06 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotes (') instead of pound signs. Example: Between '2/2/2006' And '12/1/2006'
  Not "Germany"	Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".
  Not "T*"	Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk.
  Not "*t"	Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk.
  In(Canada,UK)	In a list, finds all records containing Canada or UK.
  Like "[A-D]*"	In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk.
  Like "*ar*"	Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk.
  Like "Maison Dewe?"	Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark.
  #2/2/2006#	Finds all records for February 2, 2006. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/2006').
  < Date() - 30	Returns all dates more than 30 days old.
  Date()	Returns all records containing today's date.
  Between Date() And DateAdd("M", 3, Date())	Returns all records between today's date and three months from today's date.
  Is Null	Returns all records that contain a null (blank or undefined) value.
  Is Not Null	Returns all records that contain a value.
  ""	Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field may require a fax number, but some of your customers may not have fax machines. In that case, you enter a pair of double quotation marks with no space between them ("") instead of a number.

  
  
• On the Design tab, in the Results group, click Run. Verify that the query returns the records you want. As needed, you can select unwanted fields and press DELETE to remove them, you can drag additional fields to the design grid, and you can change your criteria until you are satisfied with the query results.
  
• Go to the next steps.

Find data in the result set

1. In the results, click the field (the column) that you want to search.
2. On the Home tab, in the Find group, click Find. –or–
   Press CTRL+F.
   The Find and Replace dialog box appears.
   
3. In the Find What box, type your search string.
4. Optionally, use the Look In list to change the field that you want to search, or to search the entire table instead. By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
   -or-
   If you decide to select another column, click the desired column in the datasheet for the table. You do not need to close the dialog box.
   
5. Optionally, in the Match list, click Any Part of Field. This provides the broadest possible search.
6. In the Search list, click All, and then click Find Next. The find operation highlights all records that contain your search string. Because you selected All in the Search list, Access cycles through all the records.
   

Find wildcard characters

• When you use the Find and Replace dialog box to search for wildcard characters, you must surround the character that you want to find in brackets, like so: [*]. You follow that rule when searching for all wildcard characters except exclamation points (!) and closing brackets (]).
• If you use a wildcard character in a replacement string, Access treats that character as a literal and writes it to your database. For example, if you search on old * and replace all records that match that pattern with new *, Access writes "new *" to all the records that matched your search string.

Find wildcard characters

1. Open the table, query result set, or form. You must open tables and result sets in Datasheet view, and you must open forms in Form view.
2. On the Home tab, in the Find group, click Find. -or-
   Press CTRL+F.
   The Find and Replace dialog box appears.
   
3. If you only want to find records that contain wildcard characters, click the Find tab. If you want to find wildcard characters and replace them with other data, click the Replace tab.
4. In the Find What box, type an opening bracket ([), the wildcard character that you want to find, and a closing bracket (]). For example, if you want to find all instances of the asterisk, type [*]. If you want to run a replace operation, type your replacement string in the Replace With box.
5. Optionally, use the Look In list to change the field that you want to search, or search the entire table instead. By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
   -or-
   If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
   
6. In the Match list, select the option that you think best applies to your data. For example, if the wildcard characters reside at the start of your records, click Start of Field. Otherwise, click Any Part of Field to return the largest possible number of results.
7. Ensure that the Search Fields As Formatted check box is selected, and then click Find Next. The find operation returns the records that contain the wildcard character. If you want to replace the wildcard, click Replace. If you are sure that search and replacement strings will give you correct results, click Replace All. However, remember that you cannot undo the results of a find-and-replace operation.

Examples of wildcards in use

Search string	Match list setting	Results
[*]	Any Part of Field	Returns all records that contain an asterisk (*). This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
	Whole Field	Returns records that consist only of an asterisk.
	Start of Field	Returns records that start with an asterisk.
*[*]*	Any Part of Field	Returns all records that contain an asterisk (*) and any surrounding text. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
	Whole Field	Same result.
	Start of Field	Same result.
[!*]	Any Part of Field	Returns all records that do not contain an asterisk. Keep in mind that this search pattern can return every letter of every word in a record when you use this setting in the Match list. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-). Note The search string *[!*]* will return records that contain asterisks because it finds all the text that surrounds the asterisk.
	Whole Field	Returns no results at all.
	Start of Field	Returns the first letter of any record that does not contain an asterisk.
ma*[ch]	Any Part of Field	Returns all records that contain "ma" and either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
	Whole Field	Returns all records that start with "ma" and end with either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
	Start of Field	Returns all records that start with "ma" and contain "c" or "h".
ma*[!ch]	Any Part of Field	Highlights the letters "m" and "a" and all text that follows those letters until it encounters a "c" or an "h". The following figures illustrate this. In other words, even though you are trying to exclude records that contain "c" and "h", you may see those records because Any Part of Field matches the text that precedes the brackets.
	Whole Field	Returns all records that do not contain a "c" or an "h" if those records end in "c" or "h". For example, the find operation does not return "manic" because the word ends with a "c", but it does return "maniacal" because characters follow the "c".
	Start of Field	Returns those records that start with "ma". Access matches any text that precedes the characters enclosed in brackets, so you may see unwanted results.

Find quotation marks and null or blank values

• Fields formatted to display a value when they are blank. For example, a format may specify that a word such as "Unknown" appears in a field until you enter a value in that field.
• Blank, unformatted fields, or null values.
• Fields that contain zero-length strings. You enter zero-length strings by typing a pair of quotation marks with no spaces between them (""). When you do that, and then move the cursor to another field, Access hides the quotation marks and the field appears to be blank. Remember that you can enter zero-length strings only in fields set to the Text, Memo, and Hyperlink data types, and that those types allow zero-length strings by default.

Find quotation marks

1. Open the table in Datasheet view, or open the form bound to that table.
2. On the Home tab, in the Find group, click Find. -or-
   Press CTRL+F.
   The Find and Replace dialog box appears.
   
3. If you only want to find quotation marks, click the Find tab. To find and replace the quotation marks, click the Replace tab.
4. In the Find What box, type a quotation mark. To replace the quotation marks with another value, enter that value in the Replace With box. Note You can leave the Replace With box blank if you want to remove the quotation marks entirely.
   
5. Optionally, use the Look In list to change the field that you want to search, or search the entire table instead. By default, the Look In list contains the name of the field that you selected in step 1. If you want to search the entire table, select the name of the table from the list.
   -or-
   If you decide to select another column, click the column you want in the datasheet for the table. You do not need to close the dialog box.
   
6. Optionally, from the Match list, select Any Part of Field. This provides the broadest possible search.
7. In the Search list, select All to search through all the records.
8. Select the Search Fields as Formatted check box. Note If Access selects the check box automatically, accept that decision unless your find operation fails.
   
9. Click Find Next to find the records. If the find operation fails, clear the Search Fields as Formatted check box and try again. If you are sure you want to replace the quotation mark with another value (or no value), click Replace. If you are confident that you are finding and replacing the correct values throughout your table, click Replace All, but remember that you cannot undo a replacement operation. If you make a mistake, you must run additional find-and-replace operations that reverse your mistake.

Find blank values

1. As needed, open the table in Datasheet view that contains the values you want to find.
2. In the table, select the field that contains the blank values that you want to find.
3. On the Home tab, in the Find group, click Find. -or-
   Press CTRL+F.
   The Find and Replace dialog box appears.
   
4. Do one of the following: Find blank fields that are formatted to show a value
   
   1. To find formatted values, click the Find tab. If you want to find the values and replace them with other data, click the Replace tab.
      
   2. In the Find What box, type the value specified by the format. To add data to the empty field, enter the new data in the Replace With box.
      
   3. In the Match list, click Whole Field.
   4. Select the Search Fields as Formatted check box.
   5. Click Find Next.

   ---

   Find blank, unformatted fields
   
   1. To find fields, click the Find tab. If you want to find the fields and add a value, click the Replace tab.
   2. In the Find What box, type Null or Is Null. If you are replacing the null value with other data, enter the new data in the Replace With box.
      
   3. In the Match list, click Whole Field.
   4. Clear the Search Fields as Formatted check box.
   5. Click Find Next.

   ---

   Find fields containing zero-length strings
   
   1. To find zero-length strings, click the Find tab. If you want to replace the strings with other data, click the Replace tab.
      
   2. In the Find What box, type a pair of quotation marks with no spaces between them (""). If you are replacing the zero-length strings with other data, enter the new data in the Replace With box.
      
   3. In the Match list, click Whole Field.
   4. Clear the Search Fields as Formatted check box.
   5. Click Find Next.

   ---

Find and Replace dialog box control reference