[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