Introduction

To filter records that display in a data sheet, first decide what column holds the value you want to filter by. Then:

• To get a list of all records that hold the exact same string as that of your column, you can:
  • Right-click the value in the column and click the Equals option. Here is an example:
     
    
  • Click the value. In the Sort & Filter section of the Ribbon, click Selection and click the Equals option

After clicking, the table or query would display only the records that share the value in the field:

If you have a set of records with a string-based column, you may want to create a list of records that share a portion of a string, also called a substring. Microsoft Access allows you to create a list based on strings that start with a certain substring, that end with a certain substring, or that include a certain substring. To start, first select the substring. Then: 

• You can right-click the selected substring and click the Contains option. Here is an example:
   
  
• In the Sort & Filter section of the ribbon, you can click Selection, and click the Contains option

When using the Contains option, you must first select a string or a substring. In some cases, you may want the string to contain one or more characters or substrings. To exercise an advanced level of control on the substring or the combination of (sub) strings, right-click any field under the column, position the mouse on Text Filters and choose one of the options. The Custom Filter dialog box would come up:

When using the Custom Filter dialog box, you can type an exact string or use some characters to create an approximate string.

If you had selected:

• Equals...: You can type the exact string or an approximate string to look for:
  • If you type an exact string, this would be the same as the Equals option we saw above except that the dialog box allows you to enter a string other than the one you would have selected from the column. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string, not part of it. For example if you type Marshall, only records that have exactly Marshall in the column would be part of the result
  • If you do not know the beginning of a string, you can precede a character or a substring with the * symbol. For example, if you type *ar*, all entries that include the substring ar would be included in the list. Examples would be Farms, Edwards, Bartlett. Here is an example:
     
    
     
    
• Does Not Equal...: You can type the exact or an approximate string to look for:
  • If you type an exact string, this would be the same as the Does Not Equal option. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string. All entries that have the substring would be excluded from the result. For example if you type Marshall, all records that do not have Marshall would be in the result
  • If you do not know the beginning of a string, you can precede a character or a substring with the * symbol. For example, if you type *ar*, all entries that include the substring ar would be excluded from the list

• Begins With...: You can type one or a few characters that the content of the values in the column should start with:
  • If you type just one character, such as c, you would get all entries that start with that character, such as Charles, Castro, or Cyrano. Using only one character could result in a too long list. Therefore, you should include as many characters as possible to get fewer results
  • You can also specify a range of characters to start the string. To specify this, you use the []. In [], type the range of alphabetical characters. For example, if you type [dh], the result would include all entries that start with d, e, f, g, or h. Examples would be: Davis, Howerson, or Hoak. Here is an example:
     
    
     
    
  • You can also specify that you want only the entries that start with specific characters. To do this, use []. In [], type the characters separated by commas. An example would be [m,c,p]. This would produce only the records where the entries start with m, c, or p, such as Cole, Miller, Pacheco, Chance, or Porter. Here is an example:
     
    
• Does No Begin With...: You can type one or a few characters that the values in the column should not start with. This would produce the opposite results of the Begins With option
• Contains...: You can type a string or an approximation to look for:
  • If you use an exact string, only the records that exactly include that string would be in the resulting list
  • If you do not know the exact string but know what substring it contains, you can use the asterisk to provide it. For example, if you provide *ar*, all entries that contain ar would be included in the result
     
    
  • You can also provide a list of OR characters that the string should contain. That is, you can ask Microsoft Access to find strings that contain this character, or that character, or that character, and so on. To do this, use [] and, in [], type the characters. An example would be [dgu]. This would produce all strings that include d, all strings that include g, and all strings that include u. Examples would be Arthur, Raymond, Bouba, Gabrielle, Maurice, or Orlando. Here is an example:
     
    
• Does Not Contain...: You can type a string to look for. Any entry that includes the string would be excluded from the list
• Ends With...: You can type one or a few characters that the content of the values in the column should end with:
  • If you type just one character, such as e, you would get all entries that end with that character, such as Jeannette, Pete, Catherine, or Maurice
  • You can also provide an approximation of the characters or substrings to end with
• Does No End With...: You can type one or a few characters that the values in the column must not end with

As opposed to filtering records on a value, you can filter the records that do not follow, or are opposed to, a certain rule. This is referred to as filtering by exclusion. To filter the records that are not conform to a criterion:

• Right-click the value of the column and click the Does Not Equal option. Here is an example that displays the Does Not Equal "MD":
   
  
• Click the value. Then, in the Sort & Filter section of the Ribbon, click Selection and click the Does Not Equals option

After clicking, the table or query would display the records that do not follow the specified criterion.

You can also filter excluding a substring. That is, you can get a list of records that do not have a certain substring. To do this, first select the substring. Then: 

• You can right-click the selected substring and click the Does Not Contain option
• In the Sort & Filter section of the ribbon, you can click Selection, and click the Does Not Contain option