Results 1 to 5 of 5
  1. #1
    anziga is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    5

    Replace a string/text in ms-access db

    I would like to replace a string(a text column record) in Access database, I tried various ways, e.g.
    Code:
    "UPDATE additems" & _
    "SET client = " & txtClient.Text.ToString() & _
    "WHERE client = " & cmbClient.Text.ToString()
    and


    Code:
    "UPDATE additems SET client = Replace(client, " & strOld & "," & strNew & ")"
    and
    Code:
    "UPDATE additems SET client = Replace(client, '" & strOld & "','" & strNew & "')"
    but it doesn't work. I'm running the query string from VB, but the error messages point to SQL string itself, not the VB code. Is there something clearly wrong, or/and is there anything else I could try?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    There are multiple problems with the examples here. The first one is:
    Code:
    "UPDATE additems" & _
    "SET client
    There is no space between linefeeds, and will throw a syntax error.

    Second, this code:
    Code:
    "UPDATE additems SET client = Replace(client, " & strOld & "," & strNew & ")"
    may not work for a number of reasons. Typically, you use this syntax:
    Code:
    "statement = '" & stringVar & "'"
    to concat a string variable into a string statement like SQL, but doing that inside of an actual function I don't think it necessary. E.G. - this might be fine:
    Code:
    "UPDATE additems SET client = Replace(client, strOld, strNew)"
    on the other hand, it might not be, and you might have to concat them out anyway:

    Code:
    "UPDATE additems SET client = 
    
    Replace(client, '" & strOld & "' , '" & strNew & "')"
    and you may even have to add the brackets to be technically correct, as I'm not sure if vb always cares for them missing (the builder does accept, but vb I don't know):

    Code:
    "UPDATE additems SET [client] = 
    
    Replace([client], '" & strOld & "' , '" & strNew & "')"
    and as a matter of fact, the last example given, when printed in the immediate window with actual string variables, returns:
    Code:
    UPDATE additems SET [client] = 
    
    Replace([client], 'String1', 'String2')
    Which of course is correct, thus it should run fine in visual basic.

  3. #3
    anziga is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    5
    Thank you for the tips. Unfortunately none of those worked. Last one resulted the following error message:
    System.Data.OleDb.OleDbException was unhandled
    ErrorCode=-2147217900
    Message="Undefined function 'Replace' in expression."
    Source="Microsoft JET Database Engine"

    I'll change the database connection and try again, if that is/was the problem.

    Actually your first tip did the trick... I changed the string to:
    Code:
    Dim strSQL As String = "UPDATE additems SET client = '" & txtClient.Text.ToString() & "' WHERE client = '" & cmbClient.Text.ToString() & "'"
    and it works as it should be. Thank you!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That means you are probably ready to use the Thread tools and mark this thread as Solved, right?

  5. #5
    anziga is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    5
    Sorry, I actually tried to look a place where to do that, now it should be OK.

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

Similar Threads

  1. Text string formatting
    By DanW in forum Forms
    Replies: 2
    Last Post: 11-02-2009, 11:11 AM
  2. replace characters in a string
    By blazixinfo@yahoo.com in forum Access
    Replies: 6
    Last Post: 08-06-2009, 03:36 PM
  3. Replies: 1
    Last Post: 07-31-2009, 03:57 AM
  4. Import XML String into Access table
    By chrisjohns in forum Programming
    Replies: 0
    Last Post: 04-16-2009, 01:47 AM
  5. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 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