[AccessD] Parsing NULL values

I may be missing something here, but why not put an If Then clause around
the area that would be affected if ExhProducts is "" or Blank.  Like this:

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
	if IsNull(rst1!ExhProducts)=False AND rst1!ExhProducts<>"" then
	    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
	end if
	rst1.MoveNext
Loop
End Sub

-----Original Message-----
From: William Hindman [mailto:wdhindman at dejpolsystems.com]
Sent: Tuesday, June 13, 2006 11: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