Question want to replace "/" with "" in a table "Master"

lokanathas

Registered User.
Local time
Today, 16:02
Joined
Aug 11, 2017
Messages
23
Kindly help with a VBA in access to replace "/" with "" in entire table, table name Master
 
instead of creating the update query, i need to create a VBA module to replace the "/" with in the text with "" in a table, every time i need to do this..
 
Then you should be able to do this in datasheet view. Click the upper left corner of the table to highlight the entire table. If one of the fields is an AutoNumber, you will have to un-highlight it. In other words this will only work with fields that are updateable.Right click at the top of the table, select "Find" and use the Search and Replace feature.MAKE SURE YOU MAKE A BACK-UP COPY OF THE TABLE BEFORE YOU DO THIS!!
 
but am able to replace only 9500 cells. not entire column of table
 
Create a query in the designer using the replace code shown above. Make sure it works.
Save the query as qryUpdReplaceBackSlash

In the VBA window where you want to run this put the following code.

Currentdb.Execute "qryUpdReplaceBackSlash", dbSeeChanges

Run the code.
 
Sorry, I did not see Moke's and your reply input when I answered.It appears you are asking how to do a VBA Sub to accomplish this? If so there are a few questions to ask before dabbling with VBA.
 
Actually, Minty's suggestion is better than what I had in mind. Give it a try and let us know how you fare.
 
Create a query in the designer using the replace code shown above. Make sure it works.
Save the query as qryUpdReplaceBackSlash

In the VBA window where you want to run this put the following code.

Currentdb.Execute "qryUpdReplaceBackSlash", dbSeeChanges

Run the code.


this is not understanding to me, am pasting this code in module & run, but its not working....
any procedure / method am following is wrong? kindly suggest
 
Make a new form, put a button on it.
On the Click event press the Event Builder and the VBA code window will open. Paste the line of code. Save the code and open the form.

Now pressing the button will run your query.

If it doesn't work tell us what error code you get.
 
Make a new form, put a button on it.
On the Click event press the Event Builder and the VBA code window will open. Paste the line of code. Save the code and open the form.

Now pressing the button will run your query.

If it doesn't work tell us what error code you get.

am getting Run-time error '3065'
 
Code:
With CurrentDb
    Dim f As DAO.Field, t As DAO.TableDef
    Set t = .TableDefs("master")
    For Each f In t.Fields
    Select Case f.Type
        Case 10, 12
            .Execute "update master set [" & f.Name & "]=replace([" & f.Name & "],'/','')"
    End Select
    Next
End With
 
am getting Run-time error '3065'
That means your query is running as a select query, not an update query.

You can't "execute" a select query, only queries that perform an action.
 
Code:
With CurrentDb
    Dim f As DAO.Field, t As DAO.TableDef
    Set t = .TableDefs("master")
    For Each f In t.Fields
    Select Case f.Type
        Case 10, 12
            .Execute "update master set [" & f.Name & "]=replace([" & f.Name & "],'/','')"
    End Select
    Next
End With

am getting error, Run time 3085:
Undefined Function 'replace' in expression
 
Add this to a standard module

Code:
Public Function replaceX(s1 As String,s2 as string,s3 as string) As String
	replaceX = Replace(s1, s2, s3) 
End Function

Change

.Execute "update master set [" & f.Name & "]=replace([" & f.Name & "],'/','')"

to

.Execute "update master set [" & f.Name & "]=replaceX([" & f.Name & "],'/','')"
 
shooting the moon here, my SQL abilities are non-existent. Have you tried making a simple update query on one field to see if it works, as Minty suggested?
 

Users who are viewing this thread

Back
Top Bottom