[AccessD] Parsing NULL values

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 






More information about the AccessD mailing list