Results 1 to 12 of 12
  1. #1
    J Bhujanga is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Colorado
    Posts
    27

    Using REPLACE Function for multiple character possibilities

    I'm now using the REPLACE function to get rid of undesirable characters in text strings, for example to get rid of all the commas, apostrophes, etc that I don't want in there. There are now about 10 different characters that I want to get out. The way I've been doing it is nesting multiple REPLACEs like:
    ...Replace(Rplace([location],",",""),"'","")...


    This works fine, but as I have had to keep adding characters to it, it's getting unwieldy. Is there a way to tell it to replace any of a set of characters with the same replacement in one shot?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What are the valid characters?
    What are invalid characters?
    Tell us more.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Consider an array of all the possible characters and loop through it, using something like
    strMystring = Replace(strMyString,aryMyArray(i),"") where i is the integer for the counter in your loop. Untested guess...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    J Bhujanga is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Colorado
    Posts
    27
    To be more specific, I need to get rid of all commas, quotes, apostrophes, semi-colons, slashes, vertical bars, and asterisks, and I want to replace them with nothing.
    I was hoping to do it with a function in the query itself rather than from vba, because I'm making other changes to a lot of other fields in the table as well. These are huge tables that we receive that we need to clean up. So, I have some UPDATE queries that I use. I was hoping there might be a function that I didn't know about that worked like the REPLACE function, only you give it multiple things to test at once. It sounds like there is no such thing.

    If I use Micron's method from vba, would I put that loop inside another loop that goes through the table record by record?

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This will read through a table (or query), scan each field (named code1 and code2 below) and omit characters specified by the line in the select case. You can use both quoted literals or ascii characters with chr$(xx).
    It may be slow, but it works. You can also use the function without the subloop, just feed it a string and it will return the string cleaned.

    Code:
    Sub subloop()
        Dim rst As DAO.Recordset
        Set rst = CurrentDb.OpenRecordset("table1")
        rst.MoveLast
        rst.MoveFirst
        With rst
            While Not .EOF
                .Edit
                !code1 = fcnClean(!code1)
                !code2 = fcnClean(!code2)
                .Update
                .MoveNext
            Wend
        End With
        set rst = nothing
    End Sub
    
    Function fcnClean(arg As String) As String
        Dim clean As String
        Dim tst As String
        Dim i As Integer
        Dim x As Integer
        i = Len(arg)
        For x = 1 To i
        tst = Mid(arg, x, 1)
        Select Case tst
            Case ",", Chr(46), "|", Chr$(34), Chr$(39), "&", "%", "#"   'and so on
            Case Else
                clean = clean & tst
        End Select
        Next x
        fcnClean = clean
         'Debug.Print clean
    End Function
    Last edited by davegri; 03-02-2017 at 01:43 AM. Reason: more

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    J,

    Do you just want numbers, a-z?
    What is it in plain English that identifies your invalid characters?

    You may use something like the following:

    Code:
    Function fExtractStr(ByVal strInString As String) As String
    ' From Dev Ashish
    '(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?
    
    '(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
    'Lower or Upper case characters.
    'You can adjust to include/exclude the characters to suit your needs --jed
    
    '************ Code Start **********
    
    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String
    
        lngLen = Len(strInString)
        strOut = ""
        For i = 1 To lngLen
            strTmp = Left$(strInString, 1)
            strInString = Right$(strInString, lngLen - i)
            'The next statement will extract BOTH Lower and Upper case chars
            If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
                (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
                'to extract just lower case, use the limit 97 - 122
                'to extract just upper case, use the limit 65 - 90
                strOut = strOut & strTmp
            End If
        Next i
        fExtractStr = strOut
    End Function

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll see if I can get a version of this to work from an Update query. It removed c and e OK.
    Code:
    Function ReplaceTest()
    Dim strString As String
    Dim i As Integer
    Dim aryMyArray(2) As Variant
    
    strString = "abcdefg"
    aryMyArray(0) = "c": aryMyArray(1) = "e"
    For i = 0 To UBound(aryMyArray) - 1
    strString = Replace(strString, aryMyArray(i), "")
    strString = Replace(strString, "x", "")
    Next
    'MsgBox strString
    End Function

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    This should work in a query on a field criteria for just Letters and numbers. Got it from http://allenbrowne.com/ValidationRule.html

    Is Null Or Not Like "*[!((a-z) or (0-9))]*"

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This
    Code:
    Function ReplaceTest(Make As String) As String
    Dim i As Integer
    Dim aryMyArray(2) As Variant
    
    'Debug.Print Make
    aryMyArray(0) = "c": aryMyArray(1) = "'"
    For i = 0 To UBound(aryMyArray) - 1
      Make = Replace(Make, aryMyArray(i), "")
    Next
    ReplaceTest = Make
    End Function
    did this
    Make Fixed
    Ford Ford
    Toyota Toyota
    Chevy hevy
    Ford Ford
    Ford's Fords
    Notice that the C and apostrophe were removed. Are you OK with altering the code for your own characters and know how to change the array size?
    Last edited by Micron; 03-02-2017 at 09:48 AM. Reason: fixed table

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think it's time we heard from J with a list of the characters to "remove/replace".

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I took it that they're in post#4. But there has been 6 posts since his/her last one, so I agree, it's time to hold off.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, it's funny how we often try to guess what the poster is trying to do, and offer our best guesses.
    I wonder what sort of specs/requirements they are working from. It would be nice if OPs would offer feedback as much/often
    as responders.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replace Function
    By razkowski in forum Queries
    Replies: 7
    Last Post: 10-08-2014, 02:28 PM
  2. Replace Function
    By thescottsman92 in forum Access
    Replies: 5
    Last Post: 09-02-2013, 01:25 AM
  3. Help with Replace Function...
    By redbull in forum Programming
    Replies: 5
    Last Post: 06-27-2013, 04:05 PM
  4. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  5. replace a character with a wildcard
    By neeedhelp in forum Programming
    Replies: 2
    Last Post: 04-11-2011, 05:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums