Remove alpha characters from a text field

[geno]

Hi, I have a text field that holds an item code for product e.g. 01245rtcf, some fields will have more alpha characters than others. All fields have the numbers first then the alpha characters follow. How can I remove all the alpha characters and leave the numeric?
Thanks

 

[Mile-O]

Put this in a module:

Call it from your form, query, etc. and pass the field as it's parameter.

Public Function RemoveAlpha(ByVal strText As String) As String

    Dim strTemp As String
    Dim intCounter As Integer

    For intCounter = 1 To Len(strText)
        If IsNumeric(Mid(strText, intCounter, 1)) Then
            strTemp = strTemp & Mid(strText, intCounter, 1)
        End If
    Next intCounter

    RemoveAlpha = strTemp

End Function

 

[geno]

Hi, I haven't done much with modules. How do I call the module into the query?
Thanks

 

[geno]

I pasted your code into a module and the first part (Public Sub RemoveAlpha(ByVal strText As String) As String) is in red print.
Does this mean an error in the code?
Thanks

 

[Mile-O]

Sorry, change Sub to Function.

 

[geno]

In the query I just have the itemcode field in the query grid, how do I call the function into the query. Thanks

 

[Mile-O]

Start a new field:

NewField: RemoveAlpha([YourField])

 

[geno]

Hi, when I run the query I get an error: Undefined function 'removealpha' in expression. I pasted your code into a module and named the module removealpha, do I have to change any of your code to match the itemcode field?
Thanks for all you help.

 

[Mile-O]

Did you put it in a form's module or an actual module?

 

[geno]

I put it into a actual module.

 

[Mile-O]

Have a look at this:

 

[geno]

I imported my table over to the DB that you sent, changed the field name and it worked.
Thanks for all your help it would have taken forever to remove the alpha characters manually.

 

[Mile-O]

No probs!