Split a field

[jnh]

Hi,

Have a table column which has data like 1, First and Last name.
Trying to split that data into 2 columns: Team_Num (which is the above 1) and Name (which is above First and Last name).Thanks for any help.jnh

 

[pbaldy]

If it has the comma, or a space, you can use the Left() and Mid() functions along with the IntStr() function to find the position of the comma.

 

[jnh]

Hi pbaldy;

Thanks for the "Welcome" from the Northern part of NV.

But I still confused:
first part of field will always be one numeric followed by a comma followed by a name (first and last name not exceeding 30 characters).
Trying to separate the numeric into a new field (A...) and put the name into another new field (B...).
Thought I had it but maybe I am not using the proper table option (make, append..) vecuase when I run it nothing happens.

Any help would be greatly appreciated...jnh

 

[nanscombe]

If it's just 1 digit followed by a comma followed by a name, ie "1,John", "2,Fred" but not "10,Jim" then it should be quite easy.

You could use an update query and update Team_Num as MID([OldField],1,1) and Team_Name as MID([OldField],3)

If the number is bigger than 1 digit then it'll be time to get creative.

You may have to create a function to do some of the work.

Public Function splitOnComma(ByVal theString, ByVal thePart As Long)
Dim lngFind As Long

lngFind = InStr(theString, ",")
If thePart = 1 Then

    splitOnComma = Mid$(theString, 1, lngFind - 1)

Else
    
    splitOnComma = Mid$(theString, lngFind + 1)

End If
End Function

You could use an update query and update Team_Num as splitOnComma([Oldield],1) and Team_Name as splitOnComma([Oldield],2)

A field called "Name" may cause you problems later on as it's a reserved word, it might be better to call it something else like Team_Name.

I hope this helps