Microsoft KB Archive/115915

= ACC: Sample Expressions to Extract Portion of Text String =

Article ID: 115915

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q115915



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



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."



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

Additional query words: parse parsing text strings fullname city state zip code part partial piece section fragment segment take use functions

Keywords: kbfaq kbhowto kbusage KB115915

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.