I've looked at this for 2 weeks - compile error type mismatch 2

[RetiredCoder]

I WAS a proggrammer 10 years ago. I told a political canidate that I would fix a db that they had already built. I built the following code to delete the numeric part of an address leaving the street name (so, 11357 N. Decatur Bvd. would give me N. Decatur Bvd.). I have consistantly recieved the "compile error type mismatch" error and can't get past it.

Can you help?
Thanks
Ron Hardesty
Old time codding...

Option Explicit
Function AlphaAddress() ' (ByVal xAddress As String) As Long

    
  '  Dim intA As Long
  '  Dim lxAddressLength As Long
  '  Dim intX As Long
    
        
   ' xAddress = "11357 N. Decatur Blvd."
    '
    ' Initialize
    
    '               intA = Len(xAddress)
    '               intA = 22
    '               intX = 0 ' number of bytes from the left
    ' lxAddressLength = 22 ' Len(xAddress) 'How many char from the right
    'lxAddressLength = 22
        
    'intX = 1
    
' CheckCharacter
    
   ' If Left$(xAddress, intX) = "0" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "1" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "2" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "3" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "4" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "5" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "6" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "7" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "8" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "9" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = "-" Then GoTo NoFoundNumeric
   ' If Left$(xAddress, intX) = " " Then GoTo NoFoundNumeric

   ' GoTo EurekaFoundIt
    
' NoFoundNumeric

    ' Chg length of xAddress
   ' lxAddressLength = 1 - lxAddressLength ' How many chars from the right
    
   ' intX = intX + 1 ' How many chars from the left
    
   ' GoTo CheckCharacter

' EurekaFoundIt
    ' figure out how many characters from the right
    ' to capture the non-numeric part of address
    
    ' a is the original length of address field
    
    
    ' lxAddressLength is the number of characters
    ' from the left to
    
   ' xAddress = Right$(xAddress, lxAddressLength)
   ' xAddress = UCase$(xAddress)
   
End Function
    'Exit Function

 

[dhookom]

Everything in your code is commented out.
What are more sample addresses? Are there values like
123 13th Street
N 675 Elm Avenue


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

maybe something more like this

Public Function getAlphaAddress2(xAddress As Variant) As String
  Dim addWords() As String
  Dim varWord As Variant
  
  xAddress = Trim(xAddress)
  
  If Not Trim(xAddress & " ") = "" Then
    addWords = Split(xAddress, " ")
    For Each varWord In addWords
      
      If IsNumeric(varWord) Then
        xAddress = Replace(xAddress, varWord, "")
      End If
    Next varWord
    xAddress = Replace(xAddress, "  ", " ")
    xAddress = Trim(UCase(xAddress))
    getAlphaAddress2 = xAddress
  End If
 End Function

test

Public Sub testGetAlpha()
  Debug.Print getAlphaAddress2(Null)
  Debug.Print getAlphaAddress2("123 13th Street")
  Debug.Print getAlphaAddress2("11357 N. Decatur Blvd.")
  Debug.Print getAlphaAddress2("N 675 Elm Avenue")
End Sub

13TH STREET
N. DECATUR BLVD.
N ELM AVENUE

 

[MajP]

As Duane pointed out there may be other exceptions that you want to handle. My address is an exception. I work in Suite 101.

So to tighten this up, I would build a function to handle exception based on the word proceeding or following.

Public Function getAlphaAddress2(xAddress As Variant) As String
  Dim addWords() As String
  Dim varWord As Variant
  Dim I As Integer
  Dim nextWord As String
  Dim previousWord As String
  xAddress = Trim(xAddress)
  
  If Not Trim(xAddress & " ") = "" Then
    addWords = Split(xAddress, " ")
    For Each varWord In addWords
      'MsgBox varWord & " " & nextWord & " " & previousWord
      If IsNumeric(varWord) Then
         If Not I = 0 Then
            previousWord = addWords(I - 1)
         End If
         If Not I = UBound(addWords) Then
            nextWord = addWords(I + 1)
         End If
         If Not isException(previousWord, varWord, nextWord) Then
           xAddress = Replace(xAddress, varWord, "")
         End If
      End If
      I = I + 1
    Next varWord
    xAddress = Replace(xAddress, "  ", " ")
    xAddress = Trim(UCase(xAddress))
    getAlphaAddress2 = xAddress
  End If
 End Function

Public Function isException(previousWord As String, currentWord, nextWord As String) As Boolean
  'Check possible exception to rule. You can make lots of rules based on the
  'current word and sorrounding words
  Select Case previousWord
    'Check for suite
    Case "Suite", "STE"
       isException = True
  End Select
End Function

test

Public Sub testGetAlpha()
  Debug.Print getAlphaAddress2(Null)
  Debug.Print getAlphaAddress2("123 13th Street")
  Debug.Print getAlphaAddress2("11357 N. Decatur Blvd.")
  Debug.Print getAlphaAddress2("N 675 Elm Avenue")
  Debug.Print getAlphaAddress2("123 Hudson Street Suite 101")
  Debug.Print getAlphaAddress2("123 Hudson Street STE 101")
End Sub

results

13TH STREET
N. DECATUR BLVD.
N ELM AVENUE
HUDSON STREET SUITE 101
HUDSON STREET STE 101

 

[RetiredCoder]

The response your reading is BEFORE I looked at the above coding. I feel I owed this e-mail as soon as possible.

Sometimes when I work on something I forget other people haven't.

What's happening is I can't get the original coding to compile

No matter what I do (commenting out code or uncomment out the code and change a data types can I get the code to compile and test.

Thanks for responding so fast. For responding at all.

I'm overwhelmed and grateful for the assistance.

Ron Hardesty

 

[Golom]

Depends on what you had commented out when you compiled but two possibilities for the failure to compile are

- You have commented out the definition of xAddress (presumably for testing) and I don't see where you have dimmed it ... which you must have done because you have [blue]Option Explicit[/blue] at the beginning. If your definition is not String or Variant then the attempt to assign a string to it will raise a type mismatch error.

- If the calling argument and return type were not commented out then you have defined the return type for the function as a Long but you are attempting to assign a string to it ... again a type mismatch error.

Even if it did compile, this line of code

lxAddressLength = 1 - lxAddressLength

is clearly a problem. It will oscillate between positive and negative values ... none of which are the number of characters on the right end of the string.

 

[RetiredCoder]

Thanks for the response on the function.

Now I need to ask for 1 more favor. I wouldn't be asking if I wasn't under the gun for this to be finished. And believe me I'm awful grateful for the assistance.

I need to make this work on an access DB. the field that I get the address line from is maddress column in the StreetNameOnly table.

SELECT maddress
FROM StreetNameOnly
WHERE ((sno)=("getalpha2"));

Thanks for all your attention and assistance!
Ron

 

[MajP]

Not sure what you are doing in the where. May need to explain better. Do you have a field called sno? Maybe something like

select getAlpha2([maddress]) as AlphaAddress from streetNameOnly

or maybe you mean

select maddress from streetNameOnly where sno = getAlpha2(maddress)

The latter seems strange it would mean that you have an alpha address field already in the database and checking for a record with a calculated field equalling the stored field.

 

[RetiredCoder]

First, thanks for the reply.

What I attempted to do here is, select the old field name (maddress) from the table named StreetNameOnly where I wanted to update the new field "SNO" with the data from function getalpha2 (from the module above called GetAlphaAddress2).

What else can I help with?

Thanks!

Ron

 

[MajP]

If you have a table with field maddress and a field SNO. If you want to update SNO to the cleaned up address, it would be something like

UPDATE streetNameOnly SET streetNameOnly.SNO = getAlphaAddress2([maddress]);

 

[RetiredCoder]

Thank You very much for all your assistance. Your help was unimaginable.

I did run the file, sorted it and sent it out to the person that asked for it. Now, I have a couple of things more to do and then this project will finish.

And so will I be finished. I won't ever volunteer again for doing computer work... I've been away from it too long.

If there is ever anything I can do for you, its yours. I'm a permanent RV'er who does photography. Auto sports (

http://www.JustPlainRacin.com)

Landscapes (

http://www.RoadKillPhotos.com)

and my personal where I will be trying to teach photography at(

http://www.RonHardestyPhotos.com).

Thanks again!

Ron

 

[MajP]

No problem, we are glad to help, and glad you got it working.