William Hindman
wdhindman at dejpolsystems.com
Tue Jun 13 14:36:18 CDT 2006
...thanks for covering my backside! :) William ----- Original Message ----- From: "A.D.TEJPAL" <adtp at hotmail.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Cc: "ADT" <adtp at airtelbroadband.in> Sent: Tuesday, June 13, 2006 2:42 PM Subject: Re: [AccessD] Parsing NULL values > William, > > Apart from Null values, it would also be desirable to cater to > following type of contents for field ExhProducts in table tblSHOWDir, each > of which should get translated to test1 in field txtExhProdCat in > tabletblExhibitorProducts > > test1 > \test1 > \test1\ > > Modified sub-routine given below should cover all such contingencies, > including Null values. > > Best wishes, > A.D.Tejpal > --------------- > > ======================================== > Sub ParseProductCategories() > Dim db As dao.Database > Dim rst1 As dao.Recordset, rst2 As dao.Recordset > Dim Rtv As Variant, Cnt As Long > > Set db = CurrentDb > Set rst1 = db.OpenRecordset("tblSHOWDIR") > Set rst2 = db.OpenRecordset("tblExhibitorProducts") > rst1.MoveFirst > Do While Not rst1.EOF > If Len(rst1!ExhProducts) > 0 Then > Rtv = Split(rst1!ExhProducts, "\") > For Cnt = 0 To UBound(Rtv) > If Len(Rtv(Cnt)) > 0 Then > rst2.AddNew > rst2!EMSID = rst1!EMSID > rst2!txtExhProdCat = Rtv(Cnt) > rst2.Update > End If > Next > Else > rst2.AddNew > rst2!EMSID = rst1!EMSID > rst2.Update > End If > > rst1.MoveNext > Loop > > rst1.Close > rst2.Close > Set rst1 = Nothing > Set rst1 = Nothing > Set db = Nothing > End Sub > ======================================== > > ----- Original Message ----- > From: William Hindman > To: Access Developers discussion and problem solving > Sent: Tuesday, June 13, 2006 22:21 > Subject: [AccessD] Parsing NULL values > > > ...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 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >