When is Null not Null 
Author Message
 When is Null not Null

I have a statement that doesn't appear to work correctly:-

    TBRecordset.edit
    if TBRecordset!Field = Null then
        TBRecordset!Field = "derrick"
        endif
    TBRecordset.update

I know that the fieeld contains Null, but the if statement is bypassed!!

Any ideas

Derrick



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null
Try this

if TBRecordset!Field & "" = "" then
    ....

then it checks for Null as well as ""

Greetings,
HM



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null
You need to use IsNull function instead =
if isnull(TBRecordset!Field) = true then
Regards
Quote:

> I have a statement that doesn't appear to work correctly:-

>     TBRecordset.edit
>     if TBRecordset!Field = Null then
>         TBRecordset!Field = "derrick"
>         endif
>     TBRecordset.update

> I know that the fieeld contains Null, but the if statement is bypassed!!

> Any ideas

> Derrick



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null
You must use
    If IsNull(TBRecordset!Field) Then
    ...

Any expression containing Null is itself Null, and therefore False. Hence
    (a = Null)
and
    (a <> Null)
both return False.

--
RobSmith


: I have a statement that doesn't appear to work correctly:-
:
:
:     TBRecordset.edit
:     if TBRecordset!Field = Null then
:         TBRecordset!Field = "derrick"
:         endif
:     TBRecordset.update
:
: I know that the fieeld contains Null, but the if statement is bypassed!!
:
: Any ideas
:
: Derrick
:
:



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null


Quote:
> Any expression containing Null is itself Null, and therefore False.
Hence
>     (a = Null)
> and
>     (a <> Null)
> both return False.

Not quite. Above examples return Null, but this is because you can't
use '=' or '<>' with Null because it is an object pointer. From the
Help:

Quote:
>> snip <<  >> snip <<  >> snip <<  >> snip <<

The null keyword represents a special value that indicates a variable
does not refer to any object. The value null may be assigned to any
class or interface variable to aid garbage collection. (Assigning the
value null to any of the items above indicates to the garbage
collection system that the object or variable is no longer in use.) It
cannot be cast to any other type, and should not be considered to have
a known numeric value.

Quote:
>> snip <<  >> snip <<  >> snip <<  >> snip <<

What you are actually doing is comparing a variant (what a Field.Value
really is) to an unknown value. Note that you can't even compare Null
with Null (a.k.a. Null = Null) because it has no numeric value!
References can only be compared using the Is keyword; and even then the
following code generates an error:

    Debug.Print (Null Is Null)

because Null although being an object pointer has no value!

To return to the original question:
to determine if a Variant variable is filled use isEmpty(), because a
variant can be set to Null too!!

To show this, create a form with a single command button and add the
following code to its Click event...

    Dim test

    If IsEmpty(test) Then MsgBox "Variable now empty"
    If test = Null Then
        MsgBox "Variable is Null"
    End If

    ' Now assign Null to variable
    test = Null
    If IsEmpty(test) Then
        MsgBox "Wheew! Variable still empty"
    ElseIf IsNull(test) Then
        MsgBox "Hmmm. It has a value of Null"
    End If

...and see what you get. Therefore, IsNull() is not safe either.

Hope this helps...

-*
Arjan D.W. de Haan

-*

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null


:
: Not quite. Above examples return Null, but this is because you can't
: use '=' or '<>' with Null because it is an object pointer. From the
: Help:
:
: >> snip <<  >> snip <<  >> snip <<  >> snip <<
: The null keyword represents a special value that indicates a variable
: does not refer to any object. The value null may be assigned to any
: class or interface variable to aid garbage collection. (Assigning the
: value null to any of the items above indicates to the garbage
: collection system that the object or variable is no longer in use.) It
: cannot be cast to any other type, and should not be considered to have
: a known numeric value.
: >> snip <<  >> snip <<  >> snip <<  >> snip <<

That's NOT referring to Visual Basic!!! (I assume it's from the C++ section
of MSDN!)

Visual Basic's Null is NOT an object variable, it is a special value of the
Variant datatype, commonly used in database applications to indicate unknown
or missing data. From the CORRECT section of MSDN:

<quote>
Expressions involving Null always result in Null. Thus, Null is said to
"propagate" through expressions; if any part of the expression evaluates to
Null, the entire expression evaluates to Null.
<snip/>
You can use the IsNull function to test if a Variant variable contains Null:

If IsNull(X) And IsNull(Y) Then
   Z = Null
Else
   Z = 0
End If
</quote>

--
RobSmith



Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null
: That's NOT referring to Visual Basic!!! (I assume it's from the C++
section
: of MSDN!)
On second thoughts, it sounds like Visual J++


Wed, 18 Jun 1902 08:00:00 GMT  
 When is Null not Null


Quote:
> That's NOT referring to Visual Basic!!! (I assume it's from the C++
section
> of MSDN!)

I hate to admit it but you're right. It comes from the Visual J++ (as
you mentioned in your next post). Stupid me...

Quote:
> You can use the IsNull function to test if a Variant variable
contains Null:

> If IsNull(X) And IsNull(Y) Then
>    Z = Null
> Else
>    Z = 0
> End If
> </quote>

I didn't say IsNull doesn't - actually I made the same statement. But I
also showed that Null not necessarily means the Variant is empty. It is
a common mistake to assume Null means a Variant is empty (I know I did
in the early years). And I could not really determine from the original
post why the field was compared to Null; I assumed it was to test for a
field value not being set.

Sorry for the misquoted part, but I hope the example helped.

...Arjan...

-*
Arjan D.W. de Haan

-*

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Null Null Null!!!!!!!!!

2. Null, Null, Null!

3. double null question [2 * Null = Null] !!!

4. Alter Table with NULL/NOT NULL options

5. Null = Null Not Working ?

6. null not null

7. To Null, or not to Null...?

8. To Null, or not to Null...?

9. Null = Null error

10. null in vb and null in sql7

11. How can I pass NULL value to WinAPI which is expecting a NULL pointer

12. IS NULL vs = NULL - ODBC Drivers

 

 
Powered by phpBB® Forum Software