Null Issue

[Sash]

I have some code where I set various fields to record set values, i.e.:

IN1MI = rs.Fields("IN1_MI")

My issue is that if this field is null, it kicks out as invalid use of null.
I'm not sure how to cleanly handle this...I'm trying to avoid and if
statement because there are so many fields.

 

[Robert Morley]

Is a "Null Issue" the same as a "non-issue"?

But seriously, how you want to handle it will depend on what you're trying
to do. Three methods come to mind, the "If" method you mentioned being the
first of them. The other two methods are:

2. Use Nz(rs.Fields("IN1_MI"),<value if null>), which will convert the Null
to whatever value you specify (or 0 or an empty string if you don't specify,
whatever's appropriate for how you declared IN1MI).

3. Declare IN1MI as a Variant. This will then allow you to handle the
Null. If you do, though, you should specify the normally-optional default
property of the Field...in other words:

IN1MI = rs.Fields("IN1_MI").Value
'or
IN1MI = rs![IN1_MI].Value

The reason for this is that if you don't, you're not assigning the field
VALUE to the variant, but rather, the field object itself. This can
sometimes produce unexpected results.


Rob

 

[John Spencer]

Is the field IN1MI set as required? That means it cannot accept Null as a
value. IF it is a text field and you have set it to allow zero length
strings then you could use the Nz Function to force a zero-length string
into the field or some other "default" value

IN1MI = Nz(rs.Fields("IN1_MI"),"My Default")

Another cause of the error could be if the field IN1MI is involved in a
relationship and the relationship does not allow nulls - not usually a
problem with an Access JET backend, but if you have an Access Project or are
linking to an SQL server, this could be a problem.

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