Splitting a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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], " "));
 
Thanx - that worked!!



Ken Snell said:
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>

David# said:
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?
Click to expand...
Click to expand...
 
Back
Top