Split 1 field into 2 1

[esteiner]

Hello, please excuse my ignorance.

I would like to split the values of a field called "ManfPartNum" into two fields ("Prefix" and "BaseNum&quot.

I have an Access 97 database of about 50,000 records for which I would like to perform this operation on a weekly basis.

The following is an example record:
ManfPartnum = TMS5220CNL

I would like to create two new fields that contain the pieces of ManfPartNum. The values should be as follows:
Prefix = "TMS"
BaseNum = "5220CNL"

"Prefix" should be all of the alpha characters until the first number. The first number and the rest of the characters should be part of "BaseNum".

Any help would be appreciated

Thank you,

Charlie

 

[SteveR77]

Charlie,

A quick question. Does the part number always follow the pattern of three characters for the prefix then the base number?

If it does then the following should work.

SELECT Table1.ManfPartnum, Left([ManfPartnum],3) AS Prefix, Right([ManfPartnum],Len([ManfPartnum])-3) AS BaseNum
FROM Table1;

 

[glalsop]

This will split the part number at the first numeric character, regardless of how many alphabetic characters precede it:

Sub test()

Dim ManfPartnum As String
Dim i As Integer
Dim prefix As String, basenum As String



ManfPartnum = "TMS5220CNL"

For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
'number found, exit
Exit For
End If
Next i

prefix = Left(str, i - 1)
basenum = Right(str, Len(str) - i + 1)

End Sub

-Gary

 

[esteiner]

I am attempting to update the value of the "Prefix" field on the current record in the recordset. See the area highlighted by "****" below. I receive the following error message: "Update or CancelUpdate without AddNew or Edit". Any help would be greatly appreciated.

intCountRecords = -Int(-DCount("ManfPartNum", strTableName))
strSQL = "SELECT * FROM " & strTableName & "" _
& " ORDER BY ManfPartNum ASC;"
Set rs = db.OpenRecordset(strSQL)
For intRecordNum = 1 To intCountRecords
If Not (rs.BOF And rs.EOF) Then
strMPN = rs!ManfPartNum
For i = 1 To Len(strMPN)
If IsNumeric(Mid(strMPN, i, 1)) Then
'number found, exit
Exit For
End If
Next i

strPrefix = Left(strMPN, i - 1)
**** rs!Prefix = strPrefix ****
strBasenum = Right(strMPN, Len(strMPN) - i + 1)
rs.MoveNext
End If
Next intRecordNum
rs.Close

 

[PHV]

Replace this:
rs!Prefix = strPrefix
By this:
rs.Edit
rs!Prefix = strPrefix
rs.Update

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[esteiner]

This worked very well for my test of 15,000 records. When I try to process the full table of 90,000 records, I receive an error message stating "Overflow". Is there a limit to how many records can be processed at once? Is the machine just running out of memory?

Any ideas?

Thanks for all of your help!

-Charlie

 

[RoyVidar]

By your variable prefix, I gather intCountRecords is an integer. Integers have a nasty habit of owerflowing when it passes 32 767, so datatype long is preferred;-)

Roy-Vidar