Re: Selecting street name from address field
Based on your examples, the simplest way is to use InStr() to find the
first space, and Mid() to return the remainder of the string, e.g.
Mid([Address], InStr([Address], " ") + 1)
On Sun, 8 Jan 2006 21:35:01 -0800, kim <kim@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
>I'm trying to select just the street names from an address field that
>contains the street numbers and names in the same field. "12347 Hickory
>Drive" or 564 "Meadows Lane". So I want to only extract the "Hickory Drive"
>and "Meadows Lane". I want to separate the numbers from the names. Any help
>will be very much appreciated. Thanks!
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
Relevant Pages
- Re: Remove a space from field data
... Use Instr to locate the first space in the string. ... A123 ABC and 1AB 123 whilst retaining the second blank space. ...
(microsoft.public.access.queries) - RE: Seperate a field value by spaces
... You should be able to use the Instr() function to find the position of the ... spaces (instr returns the position one string inside another... ... Dim iPos1 As Integer 'First space position ... Dim iPos2 As Integer 'Second space position ...
(microsoft.public.access.modulesdaovba) - Re: Parsing inconsistent data
... One approach would be to do this as a multi-step process... ... If you take your initial string and search for the first space, ... the "remainder" after that first space, if what you have left has a ...
(microsoft.public.access.queries) - Re: Split Function and Week Numbers
... > First you ltrim on the string, so there are no leading spaces. ... > Next you use strpos to determine the position of the first space. ... > With substr 0,pos you get the first word. ... > The remainder of that string, ...
(alt.php) - RE: How can I create a outlook appointment item from data in a e-m
... If you know the starting point of where the text you want occurs, use InStr ... Eric Legault (Outlook MVP, MCDBA, MCTS: ... if I have a string that looks like this: ... Once you get the text you need, it's easy to create a new appointment item: ...
(microsoft.public.outlook.program_vba) |
|
