We help IT Professionals succeed at work.

Update Table using Replace Function

mtrussell
mtrussell asked
on
I have a table called accounts.  I want to strip out characters from an account table to leave just the numeric numbers without certain characters.  I have a form and a button and what I would like to do on a double click is update all the account numbers and in another field called accountscript put the value of the account without the various characters.  

I have the following but it is not looping through the records.   How would I be able to udate the table with the new value using a simple do statement and replacing the characters?

Dim DB As Database
Dim Rst As DAO.Recordset
Dim q As String
Dim x As String
Dim y As String
Dim z As String

   
    Set DB = CurrentDb()
    Set Rst = DB.OpenRecordset("accounts", dbOpenDynaset, dbSeeChanges)
   


     
   
   
    Do
    q = Me.[Security Account No].Value
   
   
        x = Replace(q, "-", "")
        y = Replace(x, "/", "")
        z = Replace(y, ".", "")
       
        Me.AccountStrip.Value = z
       
       
        Rst.MoveNext
    Loop Until Rst.EOF
Comment
Watch Question

Commented:
do this:

rst.Edit
Me.AccountStrip.Value = z
rst.Update
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Assuming (a) you have a column in the table named "AccountStrip", you can do this:

UPDATE YourTAble SET [AccountStrip]=Replace(Replace(Replace([Security Account No],".",""), "/",""), "-","")
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
why are you doing this:

Me.AccountStrip.Value = z

?

    Do
    q = Me.[Security Account No].Value
   
   
        x = Replace(q, "-", "")
        y = Replace(x, "/", "")
        z = Replace(y, ".", "")
        RST.Edit
       RST![AccountStrip] = z   ' not Me .....
        RST.Update
       
        Rst.MoveNext
    Loop Until Rst.EOF

Author

Commented:
Thanks