Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Unanswered: Update query - Replace

    Hi,

    I have a table in MS Access which has a column holding a string value.

    say 'abcdefghijkl'

    I would like to replace jkl with xxx

    In SQL Server i would write
    SELECT REPLACE('abcdefghijkl','jkl','xxx')

    In VB i would use the REplace function.

    How do I do it in access


    An update query or code in modules ???

    It would be great if anyone can help with the query or code

    thanx in advance

    cheers!
    Kiran

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Here is an example:

    Select UserID, Replace(UserName,"Alex","Smit") as NewUserName From tblUsers;

    <pre>
    Public Function Replace(ByVal strText As String, ByVal strFind As String, _
    ByVal strReplace As String) As String

    Dim intPos As Integer
    Dim int1 As Integer

    Replace = ""

    If Nz(strText, "") = "" Then Exit Function

    intPos = InStr(1, strText, strFind)
    int1 = Len(strReplace)

    If intPos > 0 Then
    Replace = Left(strText, intPos - 1) & strReplace & _
    Mid$(strText, intPos + int1)
    End If

    End Function
    </pre>

    HTH

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    IGelin's solution is a good, general-case solution, which establishes a module you can use again and again.

    An alternative is to use a query, as below:

    UPDATE Table1 SET Table1.Field1 = Left$([Field1],InStr(1,[Field1],[String To Replace])-1) & [String To Substitute] & Mid$([Field1],InStr(1,[Field1],[String To Replace])+3)
    WHERE (((Table1.Field1) Like "*" & [String To Replace] & "*"));

    This essentially does the same thing as IGelin's function, except it's acting directly within a query.

  4. #4
    Join Date
    Feb 2002
    Posts
    8
    Thanks a lot..that thing worked..however I used the code, just in case I have a similar problem like this. ...

    I would like to ask one more thing...

    I have an existing select query named say "xxx" in Access.

    I would like to take the results into a recordset in the code.
    Can you pls. tell me how to do that ?

    I don't want the query to be written in the code though....

    cheers!
    Kiran

  5. #5
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    I updated code, so please use it rather than my prev. posting:

    Public Function Replace(ByVal strText As String, ByVal strFind As String, _
    ByVal strReplace As String) As String

    Dim intPos As Integer
    Dim int1 As Integer

    Replace = ""

    If Nz(strText, "") = "" Then Exit Function

    intPos = InStr(1, strText, strFind)
    int1 = Len(strReplace)

    If intPos > 0 Then
    Replace = Left(strText, intPos - 1) & strReplace & _
    Mid$(strText, intPos + int1)
    else
    Replace = strText ' Here is the update
    End If

    End Function

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    Here is the answer on your question:

    Dim rst as dao.recordset

    set rst=currentdb.openrecordset("xxx",dbOpenDynaset)

    ...

    your code goes here

    ...

    rst.close
    set rst=nothing

    HTH

  7. #7
    Join Date
    Feb 2002
    Posts
    8
    Thanx a lot IGelin,

    I could do that just sometime back and everything looks fine now.

    cheers!
    Kiran

Posting Permissions

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