Copying text portion of a field to a seperate field

[Steve]

Hello and thanks,

I have about 200,000 records in an Access 2002 table with a "Disc Code"
field that contains data like the following:

Disc Code
AC2004
VAX1586
TDQV12

I would like to copy the text portion of this field to a separate field
leaving the original intact for the following result:

Disc Code Manufacturer Code
AC2004 AC
VAX1586 VAX
TDQV12 TDQV

Can this be done with an Update Query and some fancy use of wildcards or is
a macro and some functions (neither of which I am not very familiar with)
necessary?

Steve

 

[Graham R Seach]

Steve,

Add the following procedure to a standard module:
Public Function RemoveNums(sText As String) As String
Dim iCtr As Integer

For iCtr = 0 To 9
sText = Replace(sText, CStr(iCtr), "")
Next iCtr

RemoveNums = sText
End Function

The you can call this function in your query, like so:
UPDATE tblMyTable SET [Manufacturer Code] = RemoveNums([Disc Code])

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Steve]

Thanks, worked perfectly, Steve.