Stripping text to a new field

[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

 

[Marshall Barton]

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")

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

Expr1: Mid(subdivision, InStr(subdivision, "LOT "))

 

[Ken Snell [MVP]]

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;

 

[Guest]

Thanx - I now have about 1 hr of "clean-up" to do as opposed to weeks of
keying!

thanx - dave

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>

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