[AccessD] Parsing NULL values

William Hindman wdhindman at dejpolsystems.com
Tue Jun 13 12:31:43 CDT 2006


...thanks Charlotte ...must be Alzheimer's setting in :)

William

----- Original Message ----- 
From: "Charlotte Foust" <cfoust at infostatsystems.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Tuesday, June 13, 2006 1:04 PM
Subject: Re: [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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 






More information about the AccessD mailing list