Replace function not working in Access

Posted on 2004-11-17
Last Modified: 2008-02-01
I dont want some of my fields to contain characters like [ * ;
Hence I am using 'replace' function in access database query to remove those characters.

I was successful in writing the same and it worked.

But since now I have changed my PC the same query is not working.

I have windows 2000 server as OS and VB6 and installed on my new machine.

I found on microsoft site that this is due to having vbajet32.dll with version 5.0 or more installed on my machine and I need to have vbajet32.dll with version (which I was unable to find anywhere) It has been mentioned that this version ships with MDAC 1.5 and I have installed MDAC 2.8

The mdac 1.5 is no longer available on any site.

I guess MDAC 1.5 may be coming with IE 4 or Win98 but not sure.

Please help. I want the query to run anyhow from my machine only.

Question by:rajasachin
    LVL 58

    Expert Comment

    Can't help you with the dll, but you might rewrite your own version of Replace, using:

        Replace(pstrString, ";", "")

    LVL 77

    Expert Comment

    This response will be of no help to you but I'm just clarifying the situation.
    From the wording of your Q I am assuming that you have a vb6 application which is using a Jet (Access, if you like) database.

    If you are referring to this article:;en-us;189448
    then it clearly states that the problem is resolved in VB6.
    It also indicates that the problem is not specific to the Replace function but applies to all functions and that it is the use of functions which is the problem, not the use of special characters.



    Author Comment

    Dear harfang
    One of my query is like this,

    UPDATE Chemistlist SET Address2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([Address2],"-"," "),"("," "),")"," "),"."," "),"`"," "),"'"," "),"*"," "),"["," "),"]"," "),","," "),"#"," "),"'"," "),"$"," "),'"'," "),"®"," ");

    and I have nearly 25 similar queries.
    The way you told me to change the queries will take a lot of time.

    Can u suggest some shortcut

    LVL 58

    Expert Comment

    You will need a custom VB function. There are several ways to do this, of course. The following is one I used to create file names from various string keys. (One client *did* in fact have a function key "CON" and "con.html" is a very bad name for writing to :)

    ''CleanName function''
    ' Converts a string from the standard ascii table (commented) to a simplified
    ' 7-bit table with all "dangerous" characters removed and in lower case.
    Function CleanName(ByVal pstrName As String) As String
        ' "________________________________ !"#$%&'()*+,-./0123456789:;<=>?"
        ' "@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~"
        ' "€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿"
        ' "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ"
    ' just clean:
    'Const cClean As String _
        = "____________________________________S_-_()___-__0123456789______" _
        & "_ABCDEFGHIJKLMNOPQRSTUVWXYZ(_)___abcdefghijklmnopqrstuvwxyz(_)-_" _
        & "E__f______S_O_________---_s_o__Y__cLxY-S_Ca_(-R-o-23-mP__10)____" _
        & "AAAAAAACEEEEIIIIDNOOOOOxOUUUUY_saaaaaaaceeeeiiii_nooooo-ouuuuy_y"
    ' also lcase:
    Const cClean As String _
        = "____________________________________s_-_()___-__0123456789______" _
        & "_abcdefghijklmnopqrstuvwxyz(_)___abcdefghijklmnopqrstuvwxyz(_)-_" _
        & "e__f______s_o_________---_s_o__y__clxy-y_ca_(-r-o-23-mp__10)____" _
        & "aaaaaaaceeeeiiiidnoooooxouuuuy_saaaaaaaceeeeiiii_nooooo-ouuuuy_y"

        Dim strClean As String
        Dim intPos As Integer
        ' Convert all chars:
        For intPos = 1 To Len(pstrName)
            strClean = strClean _
                & Mid$(cClean, Asc(Mid$(pstrName, intPos, 1)) + 1, 1)
        Next intPos
        ' MS-DOS won't like those very much...
        Select Case UCase$(strClean)
            Case "CON", "NUL" _
                , "LPT1", "LPT2", "LPT3", "LPT4" _
                , "COM1", "COM2", "COM3", "COM4"
                strClean = strClean & "_"
        End Select
        CleanName = strClean

    End Function

    Author Comment

    I really appreciate your efforts to help me out. I am really amazed to see the kind of help EE is offering.
    Thanks a lott Dear "harfang",
    I will just try this solution right now.

    LVL 58

    Expert Comment

    Before you loose any time: in VB, open "Tools / References". If one of them is marked as MISSING, this could be the cause of the problem. Of course I still suggest a custom replace function in your case :)

    Author Comment

    The Clean Name function is not working instead it corrupted my data.
    Dont Mind I had taken backup before using that.
    Its really "dangerous" yaar.
    But cant the system be corrected to use the existing function.
    LVL 58

    Accepted Solution

    Ok, i guess you could not configure it the way you need it. You do realize that it was written for a different case, right? Anyway, here is a function that does a multiple character replace (slower but perhaps easier to understand):

    Function ReplaceChars(pvarString, pstrChars As String, pstrWith As String)

        Dim lngPos As Long
        Dim strC As String
        Dim strResult As String

        If IsNull(pvarString) Then Exit Function
        For lngPos = 1 To Len(pvarString)
            strC = Mid$(pvarString, lngPos, 1)
            If InStr(pstrChars, strC) Then strC = pstrWith
            strResult = strResult & strC
        Next lngPos
        ReplaceChars = strResult

    End Function

    Call it like this:
    SELECT Address2, ReplaceChars(Address2, "-().`'*[],#'$""®"," ") FROM Chemistlist;

    And later:
    UPDATE Chemistlist SET Address2 = ReplaceChars(Address2, "-().`'*[],#'$""®"," ");

    Just for the record, the CleanName does not corrupt the data :) Maybe you don't like the output, and maybe I should have added a few comments:

    The first area is a full ASCII table (iso-8859-1 or Windows Western) with only the control characters masked. The second (the commented out definition of the constant cClean is a manual rewriting of the first, where every unwanted character has been replaced by its replacement. For example, "à" by "a", "Ý" by "Y", "®" by "R", etc. All unwanted punctuation has also been replaces by an underscore "_". In your case, you prefer a space, but the ideas is the same. The last (active) definition of cClean also tranforms the input to lowercase (why call and additional LCase() function if it can be done at the same time.

    Anyway, if you want to customize the CleanName function, copy the strings from the full ASCII table to the constant strings and simply replace any unwanted character with what you want to appear (in your case, spaces)... The important point is that the length of each string must remain the same... In other words, use this constant instead:

    Const cClean As String _
        = "________________________________ !"#$%&'()*+,-./0123456789:;<=>?" _
        & "@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~" _
        & "€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿" _
        & "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ"

    And replace the characters you want changed by the function...

    Sorry it didn't work out, I wish you better luck this time :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now