Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: Replacing characters in a field

    Help! I'm trying to replace all the "&" symbols in a field in a table. I was going to try using replace in my VB code, but everytime I do this it delete all the information in the field. As I don't know where abouts in the data the "&" symbol will appear I can't use anything that requires length information.

    Has anyone delt with this problem before?

  2. #2
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    It must not be the replace method

    It works fine

    For example:

    a = "xxx&xxx"

    MsgBox Replace(a, "&", "")


    Which is your code?

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The best thing I can think of is to retrieve the column (field) thru a recordset, run the Replace function, and then write the changes back to the table.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Smile Re: Replacing characters in a field

    The & (and other special characters) can be a royal PITA to deal with. The ampersand in Accees can indicate both a Long data type and concantenation. My suggestion is to some code like I have in this post: http://www.dbforums.com/t926445.html and the compare value is using the CHR(38) to represent the ampersand.

    Hope this helps.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Oct 2003
    Posts
    9
    Cracked it!!!!! Thanks for all your help. Here's the code I used

    Function teWt()
    Dim db As Database
    Dim a As Recordset
    Dim ab As Integer
    Set db = CurrentDb()


    Set a = db.OpenRecordset("SELECT Primary_table.CUSSUPNAME, Primary_table.PwC_DTT_Unique_ID FROM Primary_table;")

    a.FindNext "PwC_DTT_Unique_ID = 1"
    Do While a.EOF = False
    ab = a!PwC_DTT_Unique_ID



    teWt = Replace(a!Cussupname, "&", "&")

    DoCmd.RunSQL ("UPDATE Primary_table SET Primary_table.CUSSUPNAME = '" & teWt & "' WHERE ((([Primary_table].[PwC_DTT_Unique_ID])= " & ab & " ));")


    a.MoveNext
    Loop
    db.Close

Posting Permissions

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