Problem with Nulls

  • Thread starter Thread starter Sreedhar
  • Start date Start date
S

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.
 
--> 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.
Click to expand...

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

John W. Vinson [MVP]
 
John W. Vinson said:
Define the property as a Variant instead, or use a zero length string instead
of a Null: NZ([middlename], "")
Click to expand...

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

Sreedhar said:
John W. Vinson said:
Define the property as a Variant instead, or use a zero length string
instead
of a Null: NZ([middlename], "")
Click to expand...

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.
Click to expand...
 
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
..
 
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.
 
Back
Top