[AccessD] Parsing NULL values

...because dullness becomes me? :(
...thanks

William

----- Original Message ----- 
From: <DWUTKA at marlow.com>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, June 13, 2006 1:01 PM
Subject: Re: [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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>