[AccessD] Parsing NULL values

...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
>