Can you smart people make my query do more than one symbol at a time?

[Dr. Logic]

I made this query to remove symbols from my data but it only removes one symbol at a time - is there a way to make it remove two or three symbols at a time? (Not a string - but "& or % or #" kind of thing).


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

Where the plus sign is in the formula above - that;s the symbol that I am removing from that column in ACCESS - but I can only do one at a time and there are a LOT of symbols - it takes along time!

(The table is called PDW and the field is NOSYMCAT (NO SYMBOL CATALOG NUMBER!)

tanks!
doc

 

[Joe4]

I am thinking that either creating a User Defined Function in VBA and/or using Record Sets to Loop through your data and update it is probably your best bet.
Both those options will involve VBA.

Sometimes, rather than going through a long list of things it CANNOT accept, it is easier to go the other way and determine what IS acceptable.
So, other than numbers and letters, are there any symbols or punctuation that are allowed?

 

[Dr. Logic]

No, no symbols are allowed. Only Letters and numbers.

 

[welshgasman]

Post 6 here
https://stackoverflow.com/questions/36983532/replace-multiple-characters-in-a-string-variable-vba

should do the job?

 

[Joe4]

Check out the function here at the bottom of this link which removes anything that is not alphanumeric: https://stackoverflow.com/questions/2494061/removing-non-alphanumeric-characters-in-an-access-field

 

[Micron]

Check out the function here at the bottom of this link which removes anything that is not alphanumeric: https://stackoverflow.com/questions/2494061/removing-non-alphanumeric-characters-in-an-access-field

I like that code, but would change a few things. None of the string variables (thus the code that uses them) are really necessary. Trim doesn't remove inner spaces, only leading and trailing, so no point in trimming since a space isn't alphanumeric. The function would take care of it anyway. Unless you want to check the result or compare it to the original, the variables for new and original aren't needed either. The original is passed to the function (inputStr) so that can be used. The fact that the code doesn't do any comparisons suggests they're not needed. Also, each iteration of finding a letter can simply be added to the return value of the function (which doesn't actually have one, so it may as well be a sub). Sure, I left out the error handling since I just wanted to test these aspects, but this is my take:

Dim n As Integer, ascVal As Integer

If inputStr = "" Then Exit Function
 For n = 1 To Len(inputStr)
   ascVal = Asc(Mid$(inputStr, n, 1))
     Select Case ascVal
        Case 48 To 57, 65 To 90, 97 To 122
          ' if value in case, add it to the function's return string
           AlphaNumeric = AlphaNumeric & Chr(ascVal)
      End Select
    Next n

 

[james_lankford]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">I'm guessing the reason for the trim is in case someone passes in "A----------------------------------------------------------------------------------------------------"
(each - represents a space)
without the trim the loop will have to run 100 times
with the trim the loop will only run once
a pretty useless optimization considering how fast computers are, but I think that's the reason its there


I would keep the trim but put it above the
If inputStr = "" Then Exit Function
in case someone passed in "---------------------------------------------------"
(again, each - represents a space)


</code>