William Hindman
wdhindman at dejpolsystems.com
Tue Jun 13 11:51:39 CDT 2006
...given A2003 DAO ...given tblSHOWDir with 2 fields: EMSID and ExhProducts sample data 1234567890 test1\test2\test3\test4\ 2345678901 test1\test2\test3\test4/erk\ 3456789012 4567890123 test1\test2\test3\ ...given tblExhibitorProducts with 2 fields: EMSID and txtExhProdCat desired data 1234567890 test1 1234567890 test2 1234567890 test3 1234567890 test4 2345678901 test1 2345678901 test2 2345678901 test3 2345678901 test4/erk 3456789012 4567890123 test1 4567890123 test2 4567890123 test3 4567890123 test3 ...the below function parses tblSHOWDIR into tblExhibitorProducts correctly EXCEPT when field ExhProducts is blank Sub ParseProductCategories() Dim db As Database Dim rst1 As Recordset Dim rst2 As Recordset Dim txtField1 As Long Dim txtField2 As String Set db = CurrentDb Set rst1 = db.OpenRecordset("tblSHOWDIR") Set rst2 = db.OpenRecordset("tblExhibitorProducts") rst1.MoveFirst Do While Not rst1.EOF txtField1 = rst1!EMSID txtField2 = Mid(rst1!ExhProducts, InStr(rst1!ExhProducts, "\") + 1) rst2.AddNew rst2!EMSID = txtField1 rst2!txtExhProdCat = Left(rst1!ExhProducts, InStr(rst1!ExhProducts, "\") - 1) rst2.Update Do Until InStr(txtField2, "\") < 1 rst2.AddNew rst2!EMSID = txtField1 rst2!txtExhProdCat = Left(txtField2, InStr(txtField2, "\") - 1) rst2.Update txtField2 = Mid(txtField2, InStr(txtField2, "\") + 1) Loop rst1.MoveNext Loop End Sub ...how do I handle the "" or nulls? William