I need to take a column (Skills) that contains multiple codes (CO;WE;PS) etc.
and turn each code into a row. I tried using Switch, but that only works if
the Skills column has one code in it. Not sure if I need some sort of IF
InStr combo, and not sure how I would construct that. Any ideas?
If the number of codes is variable, you can't do this in a simple
Query; you'll need some VBA code to parse this field out. The Split()
function will split a string into a variable-length array, and you
could then step through that array, writing new records. Air code,
untested:
Public Sub SplitSkills(vID As Long, txtSkills As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SkillSet() As String
Dim iPos As Integer
' assuming you're putting the data into table PersonSkills
' with fields ID, long integer foreign key to the person, and
' two-byte Skill field
Set db = CurrentDb
Set rs = db.OpenRecordset("PersonSkills", dbOpenDynaset)
SkillSet = Split(txtSkills, ";")
For iPos = 0 to UBound(SkillSet)
rs.AddNew
rs!ID = vID
rs!Skill = SkillSet(iPos)
rs.Update
Next iPos
rs.Close
Set rs = Nothing
End Sub
John W. Vinson[MVP]
