Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Unanswered: How to remove embedded blanks in a string

    I have a (hopefully) quick question. Is there a function in access that will allow me to change all multiple embedded blanks to a single blank. For example, I have a string that contains
    Code:
     "now      is the   time for"
    and I want to change it to
    Code:
    "now is the time for".
    Oracle provides the replace function that will allow this, but I can't find any reference in access to do the same thing. I know that I could write a loop in the code and parse out the strings, but I would rather use a function.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Bill

    Try looking again - there is indeed a Replace function!

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Hi Bill

    Try looking again - there is indeed a Replace function!

    HTH
    Depending on the version of access. What version are we talking here?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am using access 97
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll have to roll your own in that case. 2k+ has much better string utilities.

    Here's an example:
    Code:
    Function Replace(strExpr As String, strFind As String, strReplace As String,
    Optional lngStart As Long = 1) As String
    Dim strOut As String
    Dim lngLenExpr As Long
    Dim lngLenFind As Long
    Dim lng As Long
    
    lngLenExpr = Len(strExpr)
    lngLenFind = Len(strFind)
    
    If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
    Then
    lng = lngStart
    If lng > 1 Then
    strOut = Left$(strExpr, lng - 1)
    End If
    Do While lng <= lngLenExpr
    If Mid(strExpr, lng, lngLenFind) = strFind Then
    strOut = strOut & strReplace
    lng = lng + lngLenFind
    Else
    strOut = strOut & Mid(strExpr, lng, 1)
    lng = lng + 1
    End If
    Loop
    Replace = strOut
    End If
    End Function
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thank you for the code, it is much appreciated.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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