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
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
Sort:
Show:	Y	Y	Y	Y	Y
Criteria:		<>"N"
Or:

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.

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).