Splitting a field

[Guest]

I inherited a table with a [StreetAddress] field wich includes Street Number
and Street Name (ie 155 Annoyance Blvd.) I have put all the numbers into a
Separate [StNo] field, but now need to strip the numbers from the begiinning
og the original field ... the remainig numbers are of course varying in
lengths). Can I somehow delete every thing left of a space, then clean up
after that's done?

 

[Ken Snell [MVP]]

The Left, InStr, InStrRev, Mid, and Right functions are commonly used for
parsing text strings. For what you seek to do, use Mid and InStr.

UPDATE TableName
SET [StreetAddress]=Trim(Mid([StreetAddress], InStr([StreetAddress], " "));

 

[Guest]

Thanx - that worked!!

The Left, InStr, InStrRev, Mid, and Right functions are commonly used for
parsing text strings. For what you seek to do, use Mid and InStr.

UPDATE TableName
SET [StreetAddress]=Trim(Mid([StreetAddress], InStr([StreetAddress], " "));


--

Ken Snell
<MS ACCESS MVP>

I inherited a table with a [StreetAddress] field wich includes Street
Number
and Street Name (ie 155 Annoyance Blvd.) I have put all the numbers into
a
Separate [StNo] field, but now need to strip the numbers from the
begiinning
og the original field ... the remainig numbers are of course varying in
lengths). Can I somehow delete every thing left of a space, then clean up
after that's done?