Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Question Unanswered: Remove multiple spaces

    I received some data where the address elements weren't concatenated properly.

    ie.
    100 Main St
    or
    100[tab]Main St

    I wrote a function that works with one exception. If there is no input string, then it returns #Error

    Function ProperSpace(txtin As String)

    Dim txtout As String
    Dim txtlast As String
    Dim txtnext As String

    If InStr(1, txtin, Chr(32)) Then
    'If Len(txtin) > 0 Then
    txtlast = Mid(txtin, 1, 1)
    txtout = txtlast
    For cnt = 2 To Len(txtin)
    txtnext = Mid(txtin, cnt, 1)
    If ((Asc(txtlast) = 32) And (Asc(txtnext) = 32)) Then
    txtout = txtout
    Else
    txtout = txtout + txtnext
    End If
    txtlast = txtnext
    Next cnt
    ProperSpace = Trim(txtout)
    Else
    ProperSpace = ""
    End If

    End Function
    Last edited by coders4hire; 06-17-04 at 20:03. Reason: Found close solution
    Doug

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Don't allow the function to execute, should the input string be invalid.


    1) Outside of Function

    If str.length > 0 Then
    ProperSpace(str)
    End If

    2) Inside Function

    Fucntion ProperSpace(txt As String) As Short
    If Not txt.length > 0 Then
    Return -1 // The Return value is not important
    End If
    End Function
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Worked.

    I just added the logic to the query string:
    Addr: IIf(Len([Address])>0,properspace([Address]),"")
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •