Home	Excel	Access
| Feedback | A reports manager form |

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)
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 domain extension type.

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

UserGroups:  Query
Leader	Email_Addr	Posn_Domain	Full_Domain	Main_Domain
Mr B Wayne	brw@hotmail.com	4	hotmail.com	hotmail
Mrs A Boleyn	aboleyn@heverguides.co.uk	8	heverguides.co.uk	heverguides
Miss J Valin	jvalin@nhs.com	7	nhs.com	nhs

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