One field with deliminators into 3 fields

[rw0706]

Hi, I'm looking to take a product code which is currently being entered into one field in the format of A-01-002 with a description as another field and creating 3 new fields with the text between each delimitor having it's own field.

For example the two fields txtProductNumber and txtDescription would become the following 4 fields: txtType (ie the A), txtPart (ie the 01), txtRev (ie the 002) and txt Description.

I just need to do this to update old records (~10000) as all new records will be entered in the new format.

Also, there are some really old records that have a different ProductNumber format but as long as the procedure can ignore them without crashing I can deal with them after.

Any help is appreciated, if this is doable with SQL that would be a major plus but I'm thinking it would have to be VBA. I haven't built a database in nearly a year so I'm pretty rusty.

Thanks!
rw

Small edit: I would prefer if it could detect the "-" deliminators instead of just counting over and seperating, it is possible instead of an "A" in the first field there could be "AES" or similar. Thanks!

 

[garyholc]

You could create a query, which would then create or append to a new table.

In the txtType field put "=left([txtproductnumber],1) to return "A"

Then txtPart field put "=mid([txtproductnumber],3,2) to return the "01"

And txtRev would be "=right([txtproductnumber],3) to return the "002"


This is of course assuming the field length stays constant in the data....

Edit : sorry just saw your edit. Look up a function called InStr which will locate a particular character and return its position, i.e. Instr([yourstring],"-")

So you could for example have a variable:

Dim Position1 as integer

Position1 = Instr([yourstring],"-")

which would return the first -

Then Position2 = Instr(Position1+1,[yourstring],"-")

to return the second -

Then write a bit of code to use these value to tell where the mid/right etc starts...

 

[DCrake]

You can also use the Split function to seperate the different elements in to an array

Split(YourString,"-") will create an array of each element in to something like

A
123
123

or
aed
01
001

 

[vbaInet]

The function:

Public Function GetStringPart(intPart As Byte, strProductCode As String) As Variant
    If IsNull(strProductCode) Then
        Exit Function
    End If
    
    Select Case intPart
        Case 1
            GetStringPart = Left(strProductCode, InStr(1, strProductCode, "-") - 1)
        Case 2
            Dim splitString As Variant
            
            splitString = Split(strProductCode, "-")
            
            GetStringPart = IIf(UBound(splitString) = 2, splitString(1), vbNullString)
        Case 3
            GetStringPart = Mid(strProductCode, InStrRev(strProductCode, "-") + 1)
        Case Else
            ' do nothing
    End Select
End Function

The SQL:

INSERT INTO NewTable (Field1, Field2, Field3)
      SELECT GetStringPart(1, [ProductCode]) AS F1, GetStringPart(2, [ProductCode]) AS F2, GetStringPart(3, [ProductCode]) AS F3
      FROM OldTable;

 

[rw0706]

Thanks for the replies everyone! The method vbaInet posted seems to be doing the trick for most of the entries, I'll just modify the query with if statements and change the code slightly for the 10% of the entries that are formatted differently.

The nice thing is that I only have to do this once, after the old records are converted any new entries will be in this new format.

 

[vbaInet]

Glad it worked for you!