Parsing data

[David#]

I have a field named [SubdLot]. Many of the 32000 records look something
like "The Sands - lot 16". I obviouly need to pull out everything from
"lot" and beyond and drop it into [LotNo] field (not all fields have a
hyphen... there are numerous variations!!)

Then I neeed to delete the characters "lot" from the newly created [lotno].
I'll deal with the hyphens later.

I've failed miserably trying to use InStr and Mid functions. Is there a
something i can do in query design to start cleaning this up? Thanx - Dave

 

[Ofer Cohen]

Try

Mid([SubdLot],Instr([SubdLot],"lot")+3)

That way it will return every thing after the lot, so you wont have to drop
tater the lot.

 

[Marshall Barton]

I have a field named [SubdLot]. Many of the 32000 records look something
like "The Sands - lot 16". I obviouly need to pull out everything from
"lot" and beyond and drop it into [LotNo] field (not all fields have a
hyphen... there are numerous variations!!)

Then I neeed to delete the characters "lot" from the newly created [lotno].
I'll deal with the hyphens later.

I've failed miserably trying to use InStr and Mid functions. Is there a
something i can do in query design to start cleaning this up? Thanx - Dave

LotNo: Trim(Mid(SubdLot, IIf(Instr(SubdLot, "Lot")>0,
Instr(SubdLot, "Lot") + 3, 1)))

 

[John W. Vinson]

I have a field named [SubdLot]. Many of the 32000 records look something
like "The Sands - lot 16". I obviouly need to pull out everything from
"lot" and beyond and drop it into [LotNo] field (not all fields have a
hyphen... there are numerous variations!!)

Then I neeed to delete the characters "lot" from the newly created [lotno].
I'll deal with the hyphens later.

I've failed miserably trying to use InStr and Mid functions. Is there a
something i can do in query design to start cleaning this up? Thanx - Dave

Well... be careful here. What if you have records with "New Allotment - Lot
15" or "Camelot - Lot 8"? Or "The Sands - Lt 5"?

I'd suggest two things: use a criterion of

LIKE "*Lot #*"

to select only records with the word LOT followed by a blank and a number; and
use

Mid([SubdLot], InStrRev([SubdLot], "Lot ") + 4))

to extract just the lot number (without the word LOT thanks to the +4).

You can then run a query with a criterion

NOT LIKE "*Lot #*"

to find the records which don't fit the expected pattern.

John W. Vinson [MVP]

 

[David#]

That did it - thanx - Unfortunately, I now need to delete "Lot xxx" from the
[SubdLot] field. I can isolate it but can't seem to delete just that part of
the field.

I have a field named [SubdLot]. Many of the 32000 records look something
like "The Sands - lot 16". I obviouly need to pull out everything from
"lot" and beyond and drop it into [LotNo] field (not all fields have a
hyphen... there are numerous variations!!)

Then I neeed to delete the characters "lot" from the newly created [lotno].
I'll deal with the hyphens later.

I've failed miserably trying to use InStr and Mid functions. Is there a
something i can do in query design to start cleaning this up? Thanx - Dave

Well... be careful here. What if you have records with "New Allotment - Lot
15" or "Camelot - Lot 8"? Or "The Sands - Lt 5"?

I'd suggest two things: use a criterion of

LIKE "*Lot #*"

to select only records with the word LOT followed by a blank and a number; and
use

Mid([SubdLot], InStrRev([SubdLot], "Lot ") + 4))

to extract just the lot number (without the word LOT thanks to the +4).

You can then run a query with a criterion

NOT LIKE "*Lot #*"

to find the records which don't fit the expected pattern.

John W. Vinson [MVP]

 

[John W. Vinson]

That did it - thanx - Unfortunately, I now need to delete "Lot xxx" from the
[SubdLot] field. I can isolate it but can't seem to delete just that part of
the field.

Try

UPDATE yourtable
SET [SubdLot] = Trim(Left([SubdLot], InStrRev([SubdLot], "Lot ") - 1))
WHERE [SubDLot] LIKE "*Lot #*";

Then get rid of the hyphen:

UPDATE yourtable
SET SubdLot =Trim(Left([SubdLot], Len([SubdLot]) - 1))
WHERE SubdLot LIKE "*-"

John W. Vinson [MVP]

 

[David#]

Thanx for all your help!! - Actually labored thru the clean-up using "find
and replace"

David

That did it - thanx - Unfortunately, I now need to delete "Lot xxx" from the
[SubdLot] field. I can isolate it but can't seem to delete just that part of
the field.

Try

UPDATE yourtable
SET [SubdLot] = Trim(Left([SubdLot], InStrRev([SubdLot], "Lot ") - 1))
WHERE [SubDLot] LIKE "*Lot #*";

Then get rid of the hyphen:

UPDATE yourtable
SET SubdLot =Trim(Left([SubdLot], Len([SubdLot]) - 1))
WHERE SubdLot LIKE "*-"

John W. Vinson [MVP]