Problem with Nulls

[Sreedhar]

Hi everyone!

Can somebody please tell me how to handle nulls with class objects ? Here is
a brief description.

Code behind a Form (btn):

With Customer
.CustomerID = Me.CustomerID
.FirstName = Me.FirstName
.MiddleName = Me.MiddleName
.LastName = Me.LastName
.AddCustomer
End With

In the above code,
--> I'm trying to add a customer through a Form.
--> 'Customer' is a class object.
--> 'AddCustomer' is a method that updates the fields 'CustomerID',
'FirstName' and 'LastName' (properties) to the 'tblCustomer' in the BE.
--> The problem is when one of the fields is a Null (eg. MiddleName).
The Class Property is declared as a string and failing when passed a Null.

Thanks in advance.

 

[John W. Vinson]

--> The problem is when one of the fields is a Null (eg. MiddleName).
The Class Property is declared as a string and failing when passed a Null.

Define the property as a Variant instead, or use a zero length string instead
of a Null: NZ([middlename], "")

John W. Vinson [MVP]

 

[Sreedhar]

Define the property as a Variant instead, or use a zero length string instead
of a Null: NZ([middlename], "")

Yeah, I thought about that too. But, my database will have empty strings
where null should be; and what about dates ? An empty date #12:00:00 AM# ?
Again, that's not a null. Declaring all variables (and properties) as variant
is also not very efficient.

What I'm looking for is to make my object's properties behave like fields in
a recordset. They (the fields in a recordset) handle nulls pretty well in
that the code doesn't fail and the db is not polluted with junk data where
null should be.

Can you please tell me how it can be done ? Thanks for the help.

 

[Alex Dybenko]

Hi,
if you want to handle nulls in class object - you have to use variants.
else - when you save object to database - you have to replace empty
strings/dates with null

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Define the property as a Variant instead, or use a zero length string
instead
of a Null: NZ([middlename], "")

Yeah, I thought about that too. But, my database will have empty strings
where null should be; and what about dates ? An empty date #12:00:00 AM# ?
Again, that's not a null. Declaring all variables (and properties) as
variant
is also not very efficient.

What I'm looking for is to make my object's properties behave like fields
in
a recordset. They (the fields in a recordset) handle nulls pretty well in
that the code doesn't fail and the db is not polluted with junk data where
null should be.

Can you please tell me how it can be done ? Thanks for the help.

 

[John Spencer]

Test each value to see if it is null (or a zero length string)

With Customer
.CustomerID = Me.CustomerID

If Len(Trim(me.FirstName &"")) > 0 then
.FirstName = Me.FirstName
End if

If Len(Trim(me.MiddleName &"")) > 0 then
.MiddleName = Me.MiddleName
End If

If Len(Trim(me.LastName&"")) > 0 then
.LastName = Me.LastName
End if

.AddCustomer

End With

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sreedhar]

Hi,

Thanks everyone for your valuable suggestions.

Both Alex and John have the answers and I appreciate the different
approaches available to solving my problem.

Personally, I feel that the class object should bother about each value
being passed so that the code behind the forms stays simpler.

Thus my Customer object will be like:

Public Property Let MiddleName(varNewValue as Variant)
strMiddleName = Nz(varNewValue, "")
End Property

Public Sub AddCustomer()
.....
.....
!MiddleName = IIf(Len(Trim(strMiddleName & "")) = 0, Null, strMiddleName)
.....
.....
End Sub

How about that ?

Thanks again to all of you.

 

[Alex Dybenko]

Hi,
yes, this is what I though about

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Sreedhar]

Hi,
yes, this is what I though about

Thank you.