Sample expressions to extract portion of text string in Access 2000 (209045)
The information in this article applies to:
This article was previously published under Q209045 For a Microsoft Access 97 version of this article,
see
115915. Moderate: Requires basic macro,
coding, and interoperability skills. For a Microsoft Access 2002 version of this article,
see
286238. 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 so that you can use them with
other common formats. For example, you can use the expression that is used to
extract "Doe" from "Doe, John" 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]," "))
Middle Initial
Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: P. or blank
Expression: Expr: Trim(Mid([names], InStr(1, [names], " ") + 1, IIf(InStr(InStr(1, [names], " ") + 1, [names], " ") = 0, 0, InStr(InStr(1, [names], " ") + 1, [names], " ") - InStr(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)
| Modification Type: | Major | Last Reviewed: | 8/6/2004 |
|---|
| Keywords: | kbProgramming kbhowto kbusage KB209045 |
|---|
|