N.Ha N.Ha - 4 months ago 16
SQL Question

String Extraction between two characters

I'm working in Microsoft Access 2010 and I have a table and column [tblData]![Associate Name] with data formatted like Smith, John (123456). Obviously, this shows last name, first name, and the employee ID number (which can vary between 5 to 6 digits). My issue is that I need to extract the employee ID number from this string and I'm struggling to use the correct combination of

Left
,
Right
,
Mid
with the
InStr
function.

Answer

Use InStr() to find the position of the ( character.

Next use Mid() to extract the substring starting one character after the (

And then Val() will give you a number from the remaining digits; it ignores anything after the last digit.

If you want the result as a string instead of number, you can use CStr() to convert it.

Here is an example from the Immediate window:

Associate_Name = "Smith, John (123456)"
? InStr(1, Associate_Name, "(")
 13 
? Mid(Associate_Name, InStr(1, Associate_Name, "("))
(123456)
? Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)
123456)
? Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1))
 123456 
? CStr(Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)))
123456