| Feedback | Things to do with Queries |

Finding words in text in an Access query

Wildcards can be used in criteria to select all records which contain a particular sequence of characters (i.e. a string), but sometimes it is useful to know where one string appears within another string. This would enable you to extract particular words into a separate user defined field.

The function to search for one string within another is called INSTR() and it can be used to find data in a query. It takes the form: =INSTR(Start_Posn, String_Being_Searched, Sought_String_Item, Compare_Type) It will return a value representing the position in the String_Being_Searched at which the Sought_String_Item is found.

For example, within a list of email addresses you may wish to extract the domain name.

UserGroupsDomains: Select Query (Design) ox
  user groups data source
Field:  Leader Email_Addr Posn_Domain: InStr(1,[email_addr],"@") Full_Domain: Mid([email_addr], [posn_domain]+1,100) Main_Domain: Mid([email_addr],[posn_domain]+1, InStr([posn_domain],[email_addr],".")-[posn_domain]-1)
Table:  UserGroups  UserGroups    
Show: Y Y Y Y Y
Criteria:   <>"N"    

The Posn_Domain field is using INSTR() to search for the '@' symbol within each email address field and returning the position. This calculated field value is then used in the two subsequent fields as a parameter for the MID() function which extracts a specified number of characters from a position within a string. The Main_Domain field also searches for the '.' period which follows the '@' symbol in order to exclude the main domain name.

The MID() function takes the same format as in Excel and a description of it can be found here.

UserGroupDomains: Query ox
Leader Email_Addr Posn_Domain Full_Domain Main_Domain
Mr B Wayne 4 hotmail
Mrs A Boleyn 8 heverguides
Miss J Valin 7 nhs

The function returns zero if the item being sought is not found. It could therefore be used within an IIF() statement to determine whether a string contains certain characters. For example:

=IIF(INSTR(1,[CustomerName],"SCHOOL")>0, 0.10, 0)

This would identify all records where the customers are Schools and give them a 10% discount.

The equivalent functions to INSTR() in Excel are FIND() (case sensitive) or SEARCH() (not case sensitive).

file: acc-instr.htm Page last modified Apr14 2014