Remvoing numbers from a text field

[Smythe32]

Hi
Does anyone have some code that will extract the house number from the
street name? There is a space in between the number and the street
name. All the numbers can be varied lengths. The number is the first
entry in the field.

ex: 123 main st
45466 South St

I am looking to get
123
45466

Thanks for any help

[Keith Wilby]

<Sm******@aol.comwrote in message
news:a6**********************************@8g2000hs u.googlegroups.com...

Hi
Does anyone have some code that will extract the house number from the
street name? There is a space in between the number and the street
name. All the numbers can be varied lengths. The number is the first
entry in the field.

ex: 123 main st
45466 South St

I am looking to get
123
45466

Thanks for any help

Use the InStr function to detect the position of the first space and then
use the result in the Left function to return just the number.
Incidentally, in a normalised system you would have the number stored in its
own field.

Left([MyField],InStr([MyField]," ")-1)

Keith.
www.keithwilby.com

[Salad]

Sm******@aol.com wrote:

Hi
Does anyone have some code that will extract the house number from the
street name? There is a space in between the number and the street
name. All the numbers can be varied lengths. The number is the first
entry in the field.

ex: 123 main st
45466 South St

I am looking to get
123
45466

Thanks for any help

Left(Address,Instr(Address," ")-1)

I'd only do that if you are positive the address has a space and perhaps
verify it's a number.
If Instr(Address," ") 0 then
strAddress = Left(Address,Instr(Address," ")-1)
If IsNumeric(strAddress) then Me.Streetnum = strAddress
Endif

Rome
http://www.youtube.com/watch?v=ldk9s76WLsc

[Smythe32]

On Apr 8, 11:54*am, Salad <[e-mail]:

[e-mail] wrote:

Hi
Does anyone have some code that will extract the house number from the
street name? *There is a space in between the number and the street
name. *All the numbers can be varied lengths. *The number is the first
entry in the field.

ex: *123 main st
* * * 45466 South St

I am looking to get
123
45466

Thanks for any help

Left(Address,Instr(Address," ")-1)

I'd only do that if you are positive the address has a space and perhaps
verify it's a number.
* * * * If Instr(Address," ") 0 then
* * * * * * * * strAddress = Left(Address,Instr(Address," ")-1)
* * * * * * * * If IsNumeric(strAddress) then Me.Streetnum= strAddress
* * * * Endif

Romehttp://www.youtube.com/watch?v=ldk9s76WLsc

Thank you Both. Both worked like a charm!!!

[Larry Linson]

<Sm******@aol.comwrote

Thank you Both. Both worked like a charm!!!

Apparently you did not encounter it, but be on the alert for addresses where
the number is spelled out, as in, "Twenty-one Financial Boulevard" or "One
Microsoft Way". Those might require some special handling.

Larry Linson
Microsoft Office Access MVP

[Chuck Grimsby]

On Tue, 8 Apr 2008 16:01:12 +0100, "Keith Wilby" <he**@there.com>
wrote:

><Sm******@aol.comwrote in message
news:a6**********************************@8g2000h su.googlegroups.com...

>Does anyone have some code that will extract the house number from the
street name? There is a space in between the number and the street
name. All the numbers can be varied lengths. The number is the first
entry in the field.
ex: 123 main st
45466 South St
I am looking to get
123
45466

>Use the InStr function to detect the position of the first space and then
use the result in the Left function to return just the number.
Incidentally, in a normalised system you would have the number stored in its
own field.

Addresses are "special" cases, Keith. Personally, I'm not sure they
*can* be normalized, as there are just so darn many systems out there!
How would you normalize what "Smythe32" posted along with a series of
PO Boxes.
"P.O. Box 1234" for example.

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[Keith Wilby]

"Chuck Grimsby" <c.*******@worldnet.att.net.invalidwrote in message
news:46********************************@4ax.com...

>
Addresses are "special" cases, Keith. Personally, I'm not sure they
*can* be normalized, as there are just so darn many systems out there!
How would you normalize what "Smythe32" posted along with a series of
PO Boxes.
"P.O. Box 1234" for example.

IMO your example is the first line of the address and the house number is
null.

Regards,
Keith.