Stripping text to a new field

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

Guest

I've inherited a beast with 31000 records and I need to create a new field of
data. I have a "Subdivision" text field... example of data in this field is
"THIS SUBDIV LOT 21" then "THIS SUBDIV LOT 22" then "THAT SUBDIV LOT 75" and
THAT SUBDIV LOT 76"

How can pull out "LOT xx" and put into a new field. Luckily there seems to
be nothing after the lot number if a "LOT NO" exists. (Not all records have a
"LOT NO") Thanx - Dave
 
David# said:
I've inherited a beast with 31000 records and I need to create a new field of
data. I have a "Subdivision" text field... example of data in this field is
"THIS SUBDIV LOT 21" then "THIS SUBDIV LOT 22" then "THAT SUBDIV LOT 75" and
THAT SUBDIV LOT 76"

How can pull out "LOT xx" and put into a new field. Luckily there seems to
be nothing after the lot number if a "LOT NO" exists. (Not all records have a
"LOT NO")
Click to expand...


Try this kind of expression in a calculated field in a
query:

Expr1: Mid(subdivision, InStr(subdivision, "LOT "))
 
Add new field to your table: fldLotInfo

Run this update query to copy Lot info into that field:

UPDATE TableName
SET fldLotInfo = Mid([Subdivision], InStr([Subdivision], "Lot "))
WHERE InStr([Subdivision], "Lot ") > 0;
 
Thanx - I now have about 1 hr of "clean-up" to do as opposed to weeks of
keying!

thanx - dave

Ken Snell said:
Add new field to your table: fldLotInfo

Run this update query to copy Lot info into that field:

UPDATE TableName
SET fldLotInfo = Mid([Subdivision], InStr([Subdivision], "Lot "))
WHERE InStr([Subdivision], "Lot ") > 0;

--

Ken Snell
<MS ACCESS MVP>


David# said:
I've inherited a beast with 31000 records and I need to create a new field
of
data. I have a "Subdivision" text field... example of data in this field
is
"THIS SUBDIV LOT 21" then "THIS SUBDIV LOT 22" then "THAT SUBDIV LOT 75"
and
THAT SUBDIV LOT 76"

How can pull out "LOT xx" and put into a new field. Luckily there seems to
be nothing after the lot number if a "LOT NO" exists. (Not all records
have a
"LOT NO") Thanx - Dave
Click to expand...
Click to expand...
 
Back
Top