string parsing function

[David R]

I've used OOP before and some VBA lately, but I'm still not sure how to create a function from the raw ether.
I want to take a street address and return merely the city block, i.e. 2412 Main St. becomes 2400 Main St. Apartments are always delimited by commas, i.e. 12402 Maple Dr., Apt. 101 becomes 12400 Maple Dr.

I can do this in a query, however the two different cases perplex me. Here's what I have so far:

Public Function BlockLocator(OriginalAddress As String)
Dim varLocation, varL1, varL2 As String
Dim varS, varS1, varS2 As String

If IsNull(OriginalAddress) Then End Function

varS = Len([OriginalAddress])
varS1 = InStr(0, [OriginalAddress], " ")
varL1 = Left([OriginalAddress], varS1 - 3)
varL2 = Right([OriginalAddress], varS - varS1)
varS2 = InStr(0, varL2, ",", 1)

If (varS2 > 1) Then End Function

If (varS2 = 1) Then varL2 = Left(varL2, varS2 - 1)

varLocation = varL1 + "00 " + varL2

BlockLocator = varLocation

End Function

How do I debug test this? In theory it's going to become part of an SQL query so that I can interface with the police blotter.

 

[Jack Cowley]

It would appear, if I read your explanation correctly, that there is really no difference in an apartment address or a home address. It seems that you only need to replace the last two digits in the address. If the address is ALWAYS first and ALWAYS separated by a space from the street name then I think this code will work...

Block = Left([Address], InStr(1, [Address], " ") - 3) & "00"

Addresses less than 100 come up as 00 so you may want to check for that and make appropriate adjustments.



[This message has been edited by Jack Cowley (edited 12-12-2001).]

 

[David R]

Yes, blocks already come up as 00 which is correct for my purposes. However I guess I wasn't clear in my explanation of the difference between a house address and apartment address. The end result strips off the apartment details and leaves just the block + street address.

 

[Jack Cowley]

Dim num As String

num = Left([Text0], InStr(1, [Text0], " ") - 3) & "00"

If InStr(1, [Text0], ",") = 0 Then
Me.Text0 = num & " " & Right(Trim([Text0]), Len(Trim([Text0])) - InStr(1, [Text0], " "))
Else
Me.Text0 = num & Mid([Text0], InStr(1, [Text0], " "), InStr(1, [Text0], ",") - (Len(num) + 1))
End If

Replace Text0 with the name of your field. I have assumed the there will always be a comma if there is an apartment as part of the address.

 

[David R]

Thanks Jack. I had a feeling I was making it harder than it looked.

 

[David R]

It looks like I spoke too soon. I am a little curious how to add a call to this code from a query. SQL screen?

So far I have been unable to get it to work in the form either. It keeps giving me #Error in the unbound box. The Control Source is set to =fParseAddress([Address]), which is the name of the field in question.

Here's the code behind it:
Function fParseAddress(OriginalAddress As String) As String
'Figures out the Participant Location
Dim num As String

If InStr(1, OriginalAddress, " ") = 0 Then fParseAddress = OriginalAddress

num = Left(OriginalAddress, InStr(1, OriginalAddress, " ") - 3) & "00"

If InStr(1, OriginalAddress, ",") = 0 Then
fParseAddress = num & " " & Right(Trim(OriginalAddress), Len(Trim(OriginalAddress)) - InStr(1, OriginalAddress, " "))
Else
Me.[Participant Location] = num & Mid(OriginalAddress, InStr(1, OriginalAddress, " "), InStr(1, OriginalAddress, ",") - (Len(num) + 1))
End If

End Function

 

[David R]

Anyone have thoughts on this?

 

[raskew]

Rebuilding from scratch:

(1) (table) tblAddressTest with one field, MyAddress (Text, 50)
(2) (form) frmAddressTest, bound to tblAddressTest, set to continuous forms, with two text boxes:
- MyAddress, bound to field MyAddress
- Text2, with control source set to: =BlockLocator([MyAddress])
(3) (function) BlockLocator, saved to a new module:

Public Function BlockLocator(MyAddress As String)
Dim strAddress As String
Dim strHold As String
Dim intBreak As Integer
Dim IsApt As Boolean
Dim intLen As Integer

IsApt = IIf(InStr(MyAddress, ",") > 0, True, False)
intBreak = InStr(MyAddress, " ")
intLen = IIf(IsApt, InStr(MyAddress, ",") - intBreak, Len(MyAddress) - intBreak + 1)
strHold = LTrim(Str(100 * Int(Left(MyAddress, intBreak - 1) / 100)))
strHold = strHold & Mid(MyAddress, intBreak, intLen)

BlockLocator = strHold
End Function


I?ve got this to work for me. Hopefully it?ll work for you too.

Best wishes,

Bob

 

[David R]

Thanks raskew. It was probably Operator Error before, but yours code works perfectly, so why break it?

David R