Not Sure It's Possible

[MedianUser]

Hi,
I'm not sure Access can do this, but I thought I'd ask. Is there a query that will list every character in a given field and the number of times it appears so I can identify any non alpha-numeric characters or remove any non alpha-numeric characters automatically?
I get info from many different sources about a given part number. I group these together to pull the best data for each part number. My problem is each source does something different with the part number before I get it. Part ABC-3, for example, could show up as ABC3, A-BC3, A/B-C3, etc; which makes the "group by" function useless. All I care is that ABC3 show up in that order. I've been using a replace query to remove all the common extra characters, but I find new ones here and there. It's a large database and the part numbers can be any length so manually going through each record would be a long task. Any ideas?

Thanks

 

[Access_guy49]

I actually use the find and replace function function (common in all MS office products)

I search for a given value/character, and replace it with nothing.
Make sure you set the Match: to "Any part of field" then just hit replace all.. it only takes a second.

The only other way i can think of doing it would be to get a record set, populate an array, work through the array using char() to test for specific characters and then save your data back to your table... not that it wouldn't work but it's a lot of VBA, not sure if your into that sorta thing or not.

 

[Brianwarnock]

This is not thoroughly tested but you can try this

Brian

Public Function falphanum(strOrig As String) As String

Dim c As Long
falphanum = ""
strOrig = UCase(strOrig)
For c = 1 To Len(strOrig)
If IsNumeric(Mid(strOrig, c, 1)) Then
falphanum = falphanum & Mid(strOrig, c, 1)
ElseIf Mid(strOrig, c, 1) >= "A" And Mid(strOrig, c, 1) <= "Z" Then
falphanum = falphanum & Mid(strOrig, c, 1)
Else
falphanum = falphanum
End If
Next

End Function