[AccessD] Parsing NULL values

Test for Len(rst1!EMSID & "") > 0 before you try parsing it.


Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Hindman
Sent: Tuesday, June 13, 2006 9:52 AM
To: Access Developers discussion and problem solving
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