please remove symbols in a column in an access database

[Dr. Logic]

Replace([PDW ALL]![NOSYMCAT],"+","")

Got this 2 million record ACCESS database called PDW ALL with a column called NOSYMCAT - and the NOSYMCAT is supposed to be a catalog number with NO SYMbols.

So I have been using a formula like above to remove symbols in that column but what a pain in the $$ it is! The query takes 3 minutes to run and I have to do it for each symbol! Is there a faster formula or maybe a code for it?

Here are the symbols I am removing:
+ - ( ) / \ . # ; : @ & = $ " _ (I gotta use CTR-F for those last 2)

Should I just stick with the formula above? It takes me a LONG time to run that query 16 times.

Thanks Greatly

 

[alansidman]

Are the symbols located in the same location in each record? If that is the case, then maybe a left function, or right function or a mid function to extract the non-symbols only. Alternatively, you could run VBA if-then-else to run through the recordset to replace each of the symbols.

Here is an example of how to loop through your table, but you would have to amend the action to an If-Then sequence.

vba - Code to loop through all records in MS Access - Stack Overflow

 

[Dr. Logic]

No, they are all over the place

 

[alansidman]

Try this code on your table. Make sure to change the table name (I used table1) and add in the additional variables.

Option Compare Database
Option Explicit


Private Sub Command0_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NoSymCat FROM Table1")




    rs.MoveFirst
    With rs
        Do Until .EOF
            'Perform an if then statement
            .Edit
            If InStr(!NoSymCat, "+") > 0 Then
            !NoSymCat = Replace(!NoSymCat, "+", "")
            ElseIf InStr(!NoSymCat, "-") > 0 Then
            !NoSymCat = Replace(!NoSymCat, "-", "")
            ElseIf InStr(!NoSymCat, "(") > 0 Then
            !NoSymCat = Replace(!NoSymCat, "(", "")
            ElseIf InStr(!NoSymCat, ")") > 0 Then
            !NoSymCat = Replace(!NoSymCat, ")", "")
            'Add additional ElseIf statments here.
            
            End If
            .Update
               
            'Move to the next record. Don't ever forget to do this.
        .MoveNext
        Loop
        End With


MsgBox "Finished looping through records."


rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

Forgot to mention that I put this code behind a command button on a form.

 

[Dr. Logic]

Where do I put the macro? I know what to do in excel - I just click the tab and then VIEW CODE and then I make a module - and past it in

Where do I paste it in when in access?

 

[alansidman]

With your form open in design view, add a command button. When the wizard starts after the insertion, click on cancel so that no code is attached to the button. Now right click on the button in design view and select properties. Select the Event Tab. Select the ellipsis (...) on the On Click event. The VBA window will open for the on click event for the command button. Paste your code in there. Save and Close.

 

[Dr. Logic]

I put in the code and changed the Table1 to PDW ALL and clicked the button but nothing seems to be happening

 

[alansidman]

nothing seems to be happening

Did you open up your table and see if the code has removed the symbols?

Did you get a message after the code executed?

Did you amend the code to add the remaining symbols that I had not included?

Any error messages? If so, what? and which line highlighted when debugged?

 

[Dr. Logic]

the first record in the NOSYMCAT column is this: WA-10 and fter I clicked the button it's still the same - I think your code was set to remove the "-" so i was only testing with the code as is.

There was no message at all so maybe the code did not execute?

I did not amend the code - I was just testing to see it it got the "-"

I did not get an error message - does it matter what the button is named? mine is called command 93 and I think yours is called command 0 but in the properties my button is bringing up your code.

I'm not a code writer so I'm sorry I'm not of more help.

 

[alansidman]

Try with a different symbol please. I need to try and resolve with "-". It worked with the other ones for me. I didn't test "-", but tested with "+", "(", and ")". Button name is not an issue so long as the code appears for the button indicated.