ACC: Sample Expressions to Extract Portion of Text String

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article lists sample expressions that you can use in Microsoft Access to extract a portion of a text string. These expressions are commonly used in the Update To line of an update query to place a portion of a larger field into a new field. These expressions can be adapted for use with other common formats. For example, the expression used to extract "Doe" from "Doe, John" can be used to extract "Seattle" from "Seattle, WA."

MORE INFORMATION

The following expressions are sample expressions that you can use in Microsoft Access to extract a portion of a text string.

NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the expression.

          Original Entry                  Returned by Expression
 --------------------------------------------------------------------

 [Names] = "John Doe"                      John
 Expr: Left([Names],InStr(1,[Names]," ")-1)

 [Names] = "John Doe"                      Doe
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

 [Names] = "Doe, John"                     Doe
 Expr: Left([Names],InStr(1,[Names],",")-1)

 [Names] = "Doe, John"                     John
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

 [Names] = "John P. Doe"                   Doe
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr(1,[Names],_
      " ")+1,[Names]," "))

 [Names] = "John P. Doe"                   John
 Expr: Left([Names],InStr(1,[Names]," ")-1)

 [Names] = "John P. Doe"                   P.
 Expr: Mid([Names],InStr(1,[Names]," ")+1,InStr(InStr(1,[Names],_
      " ")+1,[Names]," ")-InStr(1,[Names]," "))

 [Names] = "Doe, John P."                  P.
 Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr(1,[Names],_
      " ")+1,[Names]," "))

 [Names] = "John Doe"  or  [Names] = "John P. Doe"     Doe
 Expr: IIf(InStr(InStr([names]," ")+1,[names]," ")<>0, _
      Right([names],Len([names])-InStr(InStr([names]," ")+1, _
      [names]," ")),Right([names],Len([names])-InStr([names]," ")))

 [ZipCode] = "98052-6399"                  98052
 Expr: Left([ZipCode],5)

 [Phone] = (206) 635-7050                  206
 Expr: Mid([Phone],2,3)

 [Phone] = (206) 635-7050                  635-7050
 Expr: Right(Trim([Phone]),8)

   ARTICLE-ID: Q148287
   TITLE     : ACC2: NEATCOD2.MDB Available on MSL

   ARTICLE-ID: Q148402
   TITLE     : ACC95: NEATCODE.MDB Available on MSL