Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC: Sample Expressions to Extract Portion of Text String


View products that this article applies to.

This article was previously published under Q115915
Moderate: Requires basic macro, coding, and interoperability skills.

↑ Back to the top


Summary

This article lists sample expressions that you can use 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. You can adapt these expressions for use with other common formats. For example, the expression you would use to extract "Doe" from "Doe, John" you can also use to extract "Seattle" from "Seattle, WA."

↑ Back to the top


More information

The following expressions are sample expressions that you can use 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.

First Name

Original Entry in [Names]: "John Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

First Name

Orignial Entry in [Names]: "Doe, John"
Returned by Expression: John
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

First Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

Last Name

Original Entry in [Names]: = "John Doe"
Returned by Expression: Doe
Expression:Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

Last Name

Original Entry in [Names]: "Doe, John"
Returned by Expression: Doe
Expression: Expr: Left([Names],InStr(1,[Names],",")-1)

Last Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: Doe
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr _
(1, [Names]," ")+1,[Names]," "))

Last Name

Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: Doe
Expression: Expr: IIf(InStr(InStr([Names]," ")+1,[Names]," ") _
<>0, Right([names],Len([names])-InStr(InStr([Names]," ")+1, _
[Names]," ")),Right([Names],Len([Names])-InStr([Names]," ")))

Middle Initial

Original Entry in [Names]: "John P. Doe"
Returned by Expression: P.
Expression Expr: Trim(Mid([Names],InStr(1,[Names]," ")+1,InStr(InStr _
(1, [Names], " ")+1,[Names]," ")-InStr(1,[Names]," ")))

Middle Initial

Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

Zip Code

Original Entry in [ZipCode]: "98052-6399"
Returned by Expression: 98052
Expression: Expr: Left([ZipCode],5)

Area Code

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 425
Expression: Expr: Mid([Phone], 2, 3)

Phone Number

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 635-7050
Expression: Expr: Right(Trim([Phone]),8)
For more sample expressions, see the Neatcode.mdb sample databases. For details on how to obtain these databases, please see the following articles in the Microsoft Knowledge Base:
148287  ACC2: Neatcod2.mdb Available in Download Center

148402  ACC95: Neatcode.mdb Available in Download Center

177972  ACC97: Neatcd97.mdb Available in Download Center

↑ Back to the top


Keywords: kbfaq, kbhowto, kbusage, KB115915

↑ Back to the top

Article Info
Article ID : 115915
Revision : 3
Created on : 1/19/2007
Published on : 1/19/2007
Exists online : False
Views : 323