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

Dr. Logic

Board Regular
Joined
Jul 13, 2005
Messages
218
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
Upvote 0
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:

Code:
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
 
Upvote 0
<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>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top