I'm trying to replace all non alphanumeric characters in a column using Microsoft Access expression builder. Basically, if it is not an A through a Z (not case sensitive) or a 0 through a 9, I want it gone. I also want spaces out. Rather than using "replace()" to switch one character at a time, I'd like to either enter an array of characters ($,!,@,%,^,&,*,(, etc.) that need to be replaced by "". Or do like a regex that tells it to just strip out the non-alphanumeric characters.

Any suggestions?

With some help from someone with much more VB experience than me, I have obtained the following code. How can I implement this functionality in Access using the expression builder?:

Public Function RemoveNonAlphaNumeric(stringValue as string) as string

Dim runningValue as string
Dim character as string

For idx = 0 to len(stringValue) step 1

character = mid(stringValue, idx, 1)

Select character
Case "A", "B"
runningValue = runningValue & character
Case else
End Select

Next idx

RemoveNonAlphaNumeric = runningValue
End Function

Here is the expanded version of that function.

48 to 57 handles numerics
65 to 90 handles UPPER CASE
97 to 122 handles lower case



Rand

Thanks so much. When I try to test the code I get an error at the character line: character = Mid(stringValue, idx, 1).

The error pop-up says the following: Run-time error '5': Invalid procedure call or argument.

The value that I'm passing to the function is ABCDefghabcesdfsfasdsdf using the code RemoveNonAlphaNumeric("ABCDefghabcesdfsfasdsdf").

Thanks,
Andrew

You will get that error message if the value of idx is less than 0.



Rand

Thanks for the tip. I got it to work by changing idx = 0 to idx = 1. I tested the code on the string " abcdefghijklmnopqr stuvwxyz0123456789 "

It returned abcdefghijklmnopqrstuvwxyz0123456789 (no space at the beginning, end or middle).

This did it! Thanks so much for your help!

If anyone wants the final code, here it is:

[functional code]

Option Compare Database
Option Explicit

Public Function RemoveNonAlphaNumeric(stringValue As String) As String
Dim runningValue As String
Dim character As String
Dim idx As Integer

For idx = 1 To Len(stringValue) Step 1
character = Mid(stringValue, idx, 1)
Select Case Asc(character)
Case 48 To 57, 65 To 90, 97 To 122
runningValue = runningValue & character
Case Else
End Select
Next idx

RemoveNonAlphaNumeric = runningValue

End Function

[/functional code]

I'm a little suspicious of those index ranges. You may need to test the function without leading or trailing spaces to make certain that all the characters get properly processed. Also, add some non-alphanumeric characters to make certain that they are properly rejected.



Rand

Thanks for the tip. That is a good point. I just tried this string:

RemoveNonAlphaNumeric("~`!@#$%^&*()-_=+[{]}|\':;?/>.<, abcdefghijklmnop qrstuv~`!@#$%^&*()-_=+[{]}|\':;?/>.<, wxyz0123456~`!@#$%^&*()-_=+[{]}|\':;?/>.<, 789 ")

It returned abcdefghijklmnopqrstuvwxyz0123456789.

I think that we have it.

Thanks again for all your help! I really appreciate it.

You must do a test where the first and last characters in the test string are characters that you want. This is the only way to verify that the starting and ending point are properly set.

Test String = "abcdef"

Watch out for a missing "a" or "f". If either one is missing, the starting point or ending point will need to be adjusted.





Rand

It returned abcdef