|| Feedback | Things to do with Queries ||
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)|
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.
|Mr B Wayneemail@example.com||4||hotmail.com||hotmail|
|Mrs A Boleynfirstname.lastname@example.org||8||heverguides.co.uk||heverguides|
|Miss J Valinemail@example.com||7||nhs.com||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||© MeadInKent.co.uk 2014|