IsNull

[Guest]

I have a date field in a list box. The date contains a date (e.g. 4/4/2004) or Null. I am using IsNUll to test the date but it always returns false to me. the code is
If IsNull(Me!OrderSummary.Column(5)) Then ...
Please help.

 

[Dirk Goldgar]

I have a date field in a list box. The date contains a date (e.g.
4/4/2004) or Null. I am using IsNUll to test the date but it always
returns false to me. the code is: If
IsNull(Me!OrderSummary.Column(5)) Then ...
Please help.

I'm pretty sure the columns of a list box are always strings (as opposed
to its Value or ItemData properties), so don't test for Null, test for a
zero-length string:

If Len(Me!OrderSummary.Column(5)) = 0 Then
' no date here
End If

 

[Guest]

Thanks. Your suggestion works prefectly. However I found that IsNothing also works. Any comment if I use IsNothing?

 

[Dirk Goldgar]

Thanks. Your suggestion works prefectly. However I found that
IsNothing also works. Any comment if I use IsNothing?

IsNothing is not, AFAIK, a native Access or VB function. My guess is
that, if it works for you, it is defined in some code that you either
wrote or imported, so I can only guess what it does. Probably it returns
True if its argument is either Null or a zero-length string, and
possibly also if it Is Empty (if a variant) or Is Nothing (if an
object). Anyway, if it works that's fine, though it may be more than
you need.

 

[Jeff Conrad]

Hi Yoda,

IsNothing is not, AFAIK, a native Access or VB function.
My guess is that, if it works for you, it is defined in
some code that you either wrote or imported, so I can
only guess what it does. Probably it returns True if its
argument is either Null or a zero-length string, and
possibly also if it Is Empty (if a variant) or Is Nothing
(if an object). Anyway, if it works that's fine, though
it may be more than you need.

Just FYI, IsNothing is a function that John Viescas wrote
which does a bit more than IsNull. I've used it before as
well and it works very nicely. You can find it in his
download files if you are curious about it.

Jeff Conrad
Access Junkie
Bend, Oregon

 

[Dirk Goldgar]

Just FYI, IsNothing is a function that John Viescas wrote
which does a bit more than IsNull. I've used it before as
well and it works very nicely. You can find it in his
download files if you are curious about it.

Ah, I see. Yes, it's about what I expected, though somewhat more
comprehensive than I'd guessed (but he doesn't check an for an Object
variable being Nothing). I don't know if I'd want to treat a numeric
value of 0 as a "nothing" state, but it's a matter of personal
semantics.

 

[Guest]

Thanks ... actually I've learned a lot from you guys conversation. But I wonder why IsNull is not working for a text field with NULL. I have read the Access help many times and my understanding is IsNull is the best way to test a field with NULL
Please correct me if I am wrong.

 

[Dirk Goldgar]

Thanks ... actually I've learned a lot from you guys conversation.
But I wonder why IsNull is not working for a text field with NULL. I
have read the Access help many times and my understanding is IsNull
is the best way to test a field with NULL. Please correct me if I am
wrong.

Should work fine. Give us more details, and tell us how you've verified
that it's "not working".

 

[Jim Carlock]

Thanks ... actually I've learned a lot from you guys conversation.
But I wonder why IsNull is not working for a text field with NULL.
I have read the Access help many times and my understanding is
IsNull is the best way to test a field with NULL.

It all really depends if you mean a text field is a textbox or a database
field that contains text.

IsNull works with a database field that contains NO text, in which case
you should have no problems and IsNull returns True.

But with a textbox object, a textbox drawn onto a form, those either
hold a 0-length string or they hold a >0 length string.

I'm speaking from an unbound object perspective, and haven't used
bound textbox's in ages, but I think they'd behave just as an unbound
textbox. Binding the textbox means connecting to a DataSource, via
the DataSource and other properties that belong to a TextBox.

 

[Dirk Goldgar]

It all really depends if you mean a text field is a textbox or a
database field that contains text.

IsNull works with a database field that contains NO text, in which
case you should have no problems and IsNull returns True.

But with a textbox object, a textbox drawn onto a form, those either
hold a 0-length string or they hold a >0 length string.

This isn't strictly true. An Access text box, whether bound or unbound,
can have a Null value much more easily than it can have a zero-length
string. Getting a ZLS into one requires an explicit entry of "".

 

[Guest]

I have a text field in my database to hold a date. I defined the field as TEXT with default NULL and AZL=Yes. When I created the record, I move NULL to it : txtDate = NULL
I have another form to display txtDate with some other info in a list box. I have a botton on this form. When this button is clicked, it will check if the txtDate is NULL or not. So my code is
If IsNull(Me.ListBox.Column(5)) then ....
In my database, some of the records have date (e.g. 06/06/2004) in txtDate but some have none. But for the test, Access always return Fasle to me for any record.

 

[Dirk Goldgar]

I have a text field in my database to hold a date. I defined the
field as TEXT with default NULL and AZL=Yes. When I created the
record, I move NULL to it : txtDate = NULL.
I have another form to display txtDate with some other info in a list
box. I have a botton on this form. When this button is clicked, it
will check if the txtDate is NULL or not. So my code is: If
IsNull(Me.ListBox.Column(5)) then .....
In my database, some of the records have date (e.g. 06/06/2004) in
txtDate but some have none. But for the test, Access always return
Fasle to me for any record.

That's because the columns of a list box or combo box are always
strings. Whatever the original data type of the field that is being
displayed in a list box, it is converted to a string for display
purposes as Listbox.Column(x). Suppose that your list box's bound
column is that same column, Listbox.Column(5) -- Bound Column = 6 on the
property sheet. Then you would find that, if you selected one of those
columns with the blank txtDate, IsNull(Listbox.Value) = True while
IsNull(Listbox.Column(5)) = False.

 

[Guest]

Thanks and I will try your suggestion. I have a question before I try. The txtDate residents in the Column(5) of the list box. So can I code "IsNull(Me.ListBox.Column(5).Value) = True" ?

 

[Dirk Goldgar]

Thanks and I will try your suggestion. I have a question before I
try. The txtDate residents in the Column(5) of the list box. So can
I code "IsNull(Me.ListBox.Column(5).Value) = True" ?

No. In the first place, the Column property is a string -- well,
technically, a Variant/String -- and therefore does not have a Value
property of its own; just the string value stored in the variant. And
second, as I was trying to make clear, this value will never be Null.
If you want to know if this column is empty, just test either

If Me.Listbox.Column(5) = "" Then

or

If Len(Me.Listbox.Column(5)) = 0 Then

 

[Guest]

Yes, you are right. I did try the If Me.Listbox.Column(5) = "" before and I always got FALSE as return. I believe the only way is to use LEN as you suggested

Thank

 

[Jim Carlock]

Thanks for the correction. Most of the stuff I mess with is through
VB and are VB controls. In this case, Visual Basic 5/6 TextBoxes.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.


:
-"Jim Carlock" wrote...

It all really depends if you mean a text field is a textbox or a
database field that contains text.

IsNull works with a database field that contains NO text, in which
case you should have no problems and IsNull returns True.

But with a textbox object, a textbox drawn onto a form, those either
hold a 0-length string or they hold a >0 length string.

This isn't strictly true. An Access text box, whether bound or unbound,
can have a Null value much more easily than it can have a zero-length
string. Getting a ZLS into one requires an explicit entry of "".

 

[Brendan Reynolds]

An important difference between Access and VB in this regard is that the
default property of an Access form control is the Value property (a Variant)
while the default property of a VB form control is the Text property (a
String). That difference trips up lots of people when moving from one to the
other!

 

[Dirk Goldgar]

Yes, you are right. I did try the If Me.Listbox.Column(5) = ""
before and I always got FALSE as return. I believe the only way is
to use LEN as you suggested.

That seems strange. If the original txtDate field is Null, then
(Me.Listbox.Column(5) = "") should be True, *and*
Len(Me.Listbox.Column(5)) should be 0. I just ran a quick test to make
sure I'm not deluded, and that's the way it came out. A string with
length = 0 should be equal to "". So if you're really finding that
Me.Listbox.Column(5) <> "" for a Null source field, something very odd
is going on. At the moment, my guess is that you made a mistake either
in your test or in interpreting the results, but that may not be being
fair to you.