query just the word "lot *"

[jessebelcher]

Hello I have a file that has about 38000 records with legal descriptions of parcels for my county. But all I need is the word lot and the number after it. A few examples are this

"CRANDALL ORIGINAL BLOCK 11 LOT 15R COPPINGER ADDN"
all need is LOT 15
"CRANDALL ORIGINAL BLK 2 LOT 16 N PT PP# SH000-0050-00 CRANDALL BARBER SHOP"

"CRANDALL ORIGINAL BLOCK 6 LOTS 4-6, 11-12, 3,10"
All i need is LOT 4-6, 11-12, 3,10

As you can see there are no standard layout to this the word lot is all over the place. I want to use this for labeling parcels in ESRI. I hope this makes sense. If I need to add a sample file let me know.

[jessebelcher]

Ok im sorry all i need is to retun just the numbers from the feild. I hope some one can help.

[geekgirlau]

You can do this with a custom function:

Public Function GetLotNum(strProperty As String) As String
Dim strLot As String
Dim i As Integer


' is the word "lot" in the string?
If InStr(1, LCase(strProperty), "lot") <> 0 Then
strProperty = Trim(Mid(strProperty, InStr(1, LCase(strProperty), "lot") + 3))

' investigate 1 character at a time after the word "lot" is found
For i = 1 To Len(strProperty)
Select Case Mid(strProperty, i, 1)
' these characters indicate part of the lot number
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ",", " ", "-"
strLot = strLot & Mid(strProperty, i, 1)

' need to cope with "s" for "lots", otherwise end the function
Case Else
If strLot <> "" Then
GetLotNum = Trim(strLot)
Exit Function
End If
End Select
Next i

If strLot <> "" Then
GetLotNum = Trim(strLot)
End If
End If
End Function

To call this in a query, pass the name of the field containing the text, for example GetLotNum([MyField])