Removing non alphanumeric chars from field in table 1

[vgnmnky]

Firstly, apologies because I am very new to VBA!

I have various tables, eg tblOrders, tblDeliveries which contain the field PartNumber. As they come from different sources, sometimes the PartNumber field will have been populated in a different manner, eg with spaces, dashes etc, and without.

What I need to do is run some code to 'fix' the part numbers in these tables (imported from various spreadsheets), so that they are in a common format, with no non alpha numeric characters.

I've 'borrowed' some code which strips other chars from a user inputted text box..but how could I adapt this to go through all the PartNumber records in a particular table?

Hope someone can help! Thanks

The borrowed code is:

Public Function Strip(intext As String) As String
Dim workstring As String
Dim newstring As String
Dim x As Integer

workstring = intext
For x = 1 To Len(intext)
Select Case Mid(workstring, x, 1)
Case &quot; &quot;, &quot;-&quot;, &quot;/&quot;, &quot;\&quot;, &quot;+&quot;, &quot;(&quot;, &quot&quot;, &quot;%&quot;, &quot;.&quot;, &quot;,&quot;, &quot;$&quot;, &quot;<&quot;, &quot;>&quot;, Chr$(34)
'Invalid so do not carry forward to new
Case Else
newstring = newstring & Mid(workstring, x, 1)
End Select
Next x
Strip = newstring

 

[JeremyNYC]

Make a recordset of this field for each of your records, loop through, and apply this to the field. Something like:

sub yadda
dim db as dao.database
dim rst as dao.recordset

set db = currentdb
set rst = db.openrecordset(&quot;SELECT PartNumber FROM tblOrders&quot;, dbopendynaset)

do until rst.eof
rst.edit
rst(&quot;PartNumber&quot = Strip(nz(rst(&quot;PartNumber&quot))
rst.update
rst.movenext
loop

end sub

You'll have to do this once for each table. And it'll take a bit of time to run. And you should DEFINITELY back up before you do this.

But if Strip works, this should work.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[MichaelRed]

The 'procedure' Strip will include the commonly defined A=N character set, it would ALSO include any/all characters NOT in the case list, these could include &quot;control Codes&quot; (as illustrated w/ vbcrlf) and any &quot;High ASCII&quot; characters, and thus is not the correct procedure. While it is certainly possible to generate an exhaustive list of ASCII characters to EXCLUDE, that list is perhaps more extensive (e.g. LARGER) than the list for INCLUSION:

Public Function basAlphaN(strTxtIn As String) As String

    'Michael Red    11/5/02     To Return ONLY Alpha-Numeric Characters

    '? basAlphaN("My Dog " & vbcrlf & " has 9 Fleas")
    'MyDoghas9Fleas

    Dim strNew As String
    Dim MyChr As String * 1
    Dim Idx As Integer

    For Idx = 1 To Len(strTxtIn)
        MyChr = Mid(strTxtIn, Idx, 1)
        Select Case UCase(MyChr)
            Case "A" To "Z", "0" To "9"
            strNew = strNew & MyChr
        End Select
    Next Idx
    basAlphaN = strNew

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Yep. Looks good. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[vgnmnky]

My Thanks to both of you!
It worked with the original code, but I'll be changing the strip routine soon as suggested. I know this was a relatively straightforward bit of code, but thank you very much for helping, because going through it has helped me to understand a few more things now.
Cheers