Parse out number based on certain property descriptions

E

emeadows

Greetings all,

I have a data field that lists a "Property Description" of each
landowner in a county. This field has Acres recorded in it but it can
also contain house lot numbers and house lot dimensions. It's pretty
easy to see which is an acreage number and which isn't. So my question
is: How can I create another field for just acreage when the parse hits
one of three criteria. For example, here are a few records from my
table:

LOT 20 SKAGGS HOLLY
SURF 163/ SCARBRO
FEE 58 AC MEADOW CK
SURF 3 07/ WHITE OAK
0.42 AC SURF MEADOW GROUND
SURF 154 50/ HD WTRS
SURF 134.550 GLADE CK LE ESTER
FEE 54.96/ LESS O & G DUNLOUP
2.63 AC SURF GLADE CK NW OF

So anything that is proceeded by "SURF" or "FEE" or followed by "AC"
would be my acreage field. Where SURF stands for 'Surface Acres' FEE
is just a tax term for "You're paying taxes on acreage" and AC of
course stands for acres. Anything with LOT or without the other three
criteria would be skipped. The new field created would be a double
field.

I appreciate your time helping me with this.

Eric
 
A

Albert D.Kallal

lets see
LOT 20 SKAGGS HOLLY
Click to expand...
nothing in the above
SURF 163/ SCARBRO
Click to expand...
key word SURF...so, we get 163
FEE 58 AC MEADOW CK
Click to expand...
key word AC found....so we get 58
SURF 3 07/ WHITE OAK
Click to expand...
key word SURF...so we get 3 - what is the 07 in the above?????

The following function should do the trick . You can either use this
function directly in a report..but better would be to run a update query
that actually sets the ac value.

Also, note that the one example of SURF 3 07 is not clear, and will not work
with the given parse routines if the 07 is not to be ignored...

Public Function MyParseAC(ByVal vText As Variant) As Variant

Dim vTokens As Variant
Dim vPreTokens As Variant ' list of tokens, then featch number

Dim intT As Integer
Dim intFound As Integer


if isnull(vText) = true then
exit function
end if

vText = Replace(vText, "/", " ")

vTokens = Split(vText, " ")

vPreTokens = Split("surf,fee", ",")

If MyFind(vTokens, "AC", intFound) = True Then
' found token...take value BEFORE token
MyParseAC = CDbl(vTokens(intFound - 1))
End If

If intFound = 0 Then
' no ac...keep searching for surf..or fee
For intT = 0 To UBound(vPreTokens)

If MyFind(vTokens, CStr(vPreTokens(intT)), intFound) = True Then
' found token..take value AFTER...
MyParseAC = (vTokens(intFound + 1))
Exit For
End If

Next intT
End If


End Function


Public Function MyFind(vList As Variant, strSearchFor As String, intFound As
Integer) As Boolean

Dim intI As Integer

For intI = 0 To UBound(vList)
If vList(intI) = strSearchFor Then
MyFind = True
intFound = intI
Exit For
End If
Next intI

End Function



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal




SURF 154 50/ HD WTRS
Click to expand...


The follwing funciton should do the trick
 
E

emeadows

SURF 3 07/ WHITE OAK
key word SURF...so we get 3 - what is the 07 in the above?????
Click to expand...

It could be 3.07 acres it could also be a lot number. I'm not realy
concerned with that fraction of an acre so in this case we can ignore
it. I'll give this a try and get back with you.

Thanks,

Eric
 
E

emeadows

Albert,

That worked GREAT. Of course there were some other unforeseen records
(that I didn't give you as examples) that threw a curve in there, but
with the help of your code I was able to modify to make it work.

It's always great when the folks giving the examples add in extra
comments about why or how they wrote their code so we can learn more
from it.

Thanks again,

Eric
 
Top