remove any letters in a column -use Update Query & how? (1 Viewer)

alienscript

Registered User.
Local time
Yesterday, 23:52
Joined
Jul 17, 2004
Messages
20
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
 

Attachments

  • table.zip
    2.2 KB · Views: 125

Mile-O

Back once again...
Local time
Today, 06:52
Joined
Dec 10, 2002
Messages
11,316
Put htis function in a module:

Code:
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

Back once again...
Local time
Today, 06:52
Joined
Dec 10, 2002
Messages
11,316
Put htis function in a module:

Code:
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

Registered User.
Local time
Yesterday, 23:52
Joined
Jul 17, 2004
Messages
20
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

AWF VIP
Local time
Today, 01:52
Joined
Jun 2, 2001
Messages
2,734
Try changing:
SET MyField = RemoveText([MyField]);

to

SET MyField = RemoveAlpha([MyField]);
 

Users who are viewing this thread

Top Bottom