importing data from a field with multiple pieces of information

[reena]

Hi,

I have a table called "Member Information" which has a field called "List". The relation is a member can belong to many list types.

For eg:
Individual ID : 2
list_type: Agriculture;Pharmaceutical;Bio

I need it split in this manner
Individual ID list_type
2 Agriculture
2 pharmaceutical
2 Bio

If any one can help me with the code in VB, I would be very grateful.

Basically I am very new to the programming area.

Thanks a lot

 

[D-Fresh]

Put the following code into a module and run it... This will Open your original table, read line by line, and put the split result into the new table along with the Location ID.

Sub ConvertField()
    Dim MyDB As Database
    Dim MyRecs As Recordset
    Dim MyRecs2 As Recordset
    Dim OldPos As Integer
    tempID As Integer
    
    Set MyDB = CurrentDb
    Set MyRecs = MyDB.OpenRecordset("OriginalTableName")
    Set MyRecs2 = MyDB.OpenRecordset("NewTableName")
    
    While Not MyRecs.EOF
        tempID = MyRecs![Individual ID]
        OldPos = 1
    
        With MyRecs2
            While InStr(OldPos + 1, ![ListField], ";") <> 0
                .AddNew
                !NewIDFiled = tempID
                !NewListField = Mid(![ListField], OldPos, InStr(OldPos + 1, ![ListField], ";") - OldPos)
                OldPos = InStr(OldPos + 1, ![ListField], ";") + 1
                .Update
            Wend
            .AddNew
            !NewIDFiled = tempID
            !NewListField = Mid(![ListField], OldPos)
            .Update
        End With
        
        MyRecs.MoveNext
    Wend
    
End Sub

I hope this helps and let me know if you need anything else.

Doug