remove any letters in a column -use Update Query & how?

[alienscript]

Hi the expert,

I have a column named "PartNumber" in my table and I want to remove any letters that are found in the PartNumbers so that it would looks like in table 2.

I can use Edit\Replace with...Match any part of Field, but I must use a query to do the job of removing the Letters at end of the values because I want to use RunMacro to open all my queries in sequence.

How to use update query ? or use a VBA code ?
Can someone help me with this and thanks a lot in advance.


[original table 1]
PartNumber
1009102
67730W
0100-09106B
0020-09107
0100-09107W
32000456612
5100-99088S
0100-09109
0100-09110A
0100-09113W
50414709000W


[desired final table 2]
PartNumber
1009102
67730
0100-09106
0020-09107
0100-09107
32000456612
5100-99088
0100-09109
0100-09110
0100-09113
50414709000

 

[Mile-O]

Put htis function in a module:

Public Function RemoveAlpha(ByVal strCode As String) As String

    Dim intTemp As Integer
    Dim byt As Byte

    For byt = 1 To Len(strCode)
    intTemp = Asc(UCase(Mid(strCode, byt, 1)))
        If intTemp < 65 Or intTemp > 90 Then
            RemoveAlpha = RemoveAlpha & Chr(intTemp)
        End If
    Next byt

End Function

Call it in the query:

i.e.

UPDATE MyTable
SET MyField = RemoveText([MyField]);

 

[Mile-O]

Put htis function in a module:

Public Function RemoveAlpha(ByVal strCode As String) As String

    Dim intTemp As Integer
    Dim byt As Byte

    For byt = 1 To Len(strCode)
    intTemp = Asc(UCase(Mid(strCode, byt, 1)))
        If intTemp < 65 Or intTemp > 90 Then
            RemoveAlpha = RemoveAlpha & Chr(intTemp)
        End If
    Next byt

End Function

Call it in the query:

i.e.

UPDATE MyTable
SET MyField = RemoveText([MyField]);

 

[alienscript]

Hi McAbney,

I'm sorry as m new in Access and need your further help.
Assuming the table name is called "MyTable" and the Field named PartNumber is in the first column of this table, I'm not sure I'm doing it right in the following steps:

-create a New query in Design View
-Add "MyTable" to Design View
-Drag the the Field "PartNumber" from "MyTable" to the GBE
-Select Update query and in "Update To", type as:

UPDATE MyTable
SET PartNumber = RemoveText([PartNumber]);

I run this query and see this error syntax: "The Expression you entered contains invalid syntax". I sure must have done the wrong steps. Would you be nice to help me with the correct steps ?

Thank you very very much.

 

[raskew]

Try changing:
SET MyField = RemoveText([MyField]);

to

SET MyField = RemoveAlpha([MyField]);