Null = Null Not Working ? 
Author Message
 Null = Null Not Working ?

Why does this not work, surely Null should = Null, but this code always
executes the Else statement.

If Null = Null Then
    [ID] = 1
Else
    [ID] = rst!MaxOfID + 1
End If

* The first Null should read rst!MaxOfID which returns Null if no records
are found. If records are found then the code works great.

Thanks Kevin

THORNBERRY ANIMAL SANCTUARY
http://www.*-*-*.com/



Sat, 31 May 2003 14:31:21 GMT  
 Null = Null Not Working ?
Solved problem with this code, but would still like to know if Null = Null
on other peoples PC.

Set rst = dbs.OpenRecordset("SELECT [Relinquishment Table].* FROM
[Relinquishment Table]")
If Not rst.EOF Then
    Set rst = dbs.OpenRecordset("SELECT Max([Relinquishment Table].ID) AS
MaxOfID FROM [Relinquishment Table];")
    [ID] = rst!MaxOfID + 1
Else
    [ID] = 1
End If

 THORNBERRY ANIMAL SANCTUARY
 http://go.to/thornberry

............................................................................
..............................


Quote:
> Why does this not work, surely Null should = Null, but this code always
> executes the Else statement.

> If Null = Null Then
>     [ID] = 1
> Else
>     [ID] = rst!MaxOfID + 1
> End If

> * The first Null should read rst!MaxOfID which returns Null if no records
> are found. If records are found then the code works great.

> Thanks Kevin

> THORNBERRY ANIMAL SANCTUARY
> http://go.to/thornberry



Sat, 31 May 2003 14:39:56 GMT  
 Null = Null Not Working ?
No. Null does not equal Null.

Null means "Unknown". The answer to the question:
        Is Unknown-value equal to Unknown-value?
is:
        Unknown
i.e. there's no way to know if the unknowns are equal.

For details on this and several related issues, see article:
        Common errors with Null
at:
        http://www.wa.apana.org.au/~abrowne/casu-12.html

The immediate answer you are looking for will involve the IsNull()
function, i.e.:
        If IsNull(SomeValue) Then
          MsgBox "Yep: it's Null"
        Else
          MsgBox "No: not Null"
        End If

Quote:

> Why does this not work, surely Null should = Null, but this code always
> executes the Else statement.

> If Null = Null Then
>     [ID] = 1
> Else
>     [ID] = rst!MaxOfID + 1
> End If

> * The first Null should read rst!MaxOfID which returns Null if no records
> are found. If records are found then the code works great.

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne


Sat, 31 May 2003 16:08:56 GMT  
 Null = Null Not Working ?
Hi,

Null means not available, not applicable, unknown, etc. A standard example
is "Does the hair color of a bald man (hair color is not applicable) is
equal to the color of his car (he don't own any car)? " so, does null = null
?  the answer is Null (don't know). That's for SQL. ADO has decided that
null is equal to any other null in the recordset method FIND, but that's
considered a boo-boo by some people. There are also SQL cases where a null
is implicitly equal to any other null (like an index no duplication, null
allowed) where all nulls will be considered ... equal... but that are the
exceptions and in general, Null is not equal to another null.

Vanderghast, Access MVP.


Quote:
> Solved problem with this code, but would still like to know if Null = Null
> on other peoples PC.

> Set rst = dbs.OpenRecordset("SELECT [Relinquishment Table].* FROM
> [Relinquishment Table]")
> If Not rst.EOF Then
>     Set rst = dbs.OpenRecordset("SELECT Max([Relinquishment Table].ID) AS
> MaxOfID FROM [Relinquishment Table];")
>     [ID] = rst!MaxOfID + 1
> Else
>     [ID] = 1
> End If

>  THORNBERRY ANIMAL SANCTUARY
>  http://go.to/thornberry

............................................................................

- Show quoted text -

Quote:
> ..............................



> > Why does this not work, surely Null should = Null, but this code always
> > executes the Else statement.

> > If Null = Null Then
> >     [ID] = 1
> > Else
> >     [ID] = rst!MaxOfID + 1
> > End If

> > * The first Null should read rst!MaxOfID which returns Null if no
records
> > are found. If records are found then the code works great.

> > Thanks Kevin

> > THORNBERRY ANIMAL SANCTUARY
> > http://go.to/thornberry



Sat, 31 May 2003 20:03:20 GMT  
 Null = Null Not Working ?
Well already a ton of people have indicated Null is not equal to Null.
For recordsets .EOF works very well.
I tend to use the IsNull function a lot to check fields.
if IsNull([Something]) then
This doesn't always work well for objects, if the value for a particular
object or object property is not null, but just plain not available.

Pai Chung


Quote:
> Why does this not work, surely Null should = Null, but this code always
> executes the Else statement.

> If Null = Null Then
>     [ID] = 1
> Else
>     [ID] = rst!MaxOfID + 1
> End If

> * The first Null should read rst!MaxOfID which returns Null if no records
> are found. If records are found then the code works great.

> Thanks Kevin

> THORNBERRY ANIMAL SANCTUARY
> http://go.to/thornberry



Sat, 31 May 2003 22:57:07 GMT  
 Null = Null Not Working ?
Cheers guys,

I now understand Null, and it seems the best solution to my problem is the
IsNull() function.
Thanks
Kevin


Quote:
> Well already a ton of people have indicated Null is not equal to Null.
> For recordsets .EOF works very well.
> I tend to use the IsNull function a lot to check fields.
> if IsNull([Something]) then
> This doesn't always work well for objects, if the value for a particular
> object or object property is not null, but just plain not available.

> Pai Chung



> > Why does this not work, surely Null should = Null, but this code always
> > executes the Else statement.

> > If Null = Null Then
> >     [ID] = 1
> > Else
> >     [ID] = rst!MaxOfID + 1
> > End If

> > * The first Null should read rst!MaxOfID which returns Null if no
records
> > are found. If records are found then the code works great.

> > Thanks Kevin

> > THORNBERRY ANIMAL SANCTUARY
> > http://go.to/thornberry



Sun, 01 Jun 2003 05:38:05 GMT  
 Null = Null Not Working ?
Yes Michel.... How about adding this to the FAQ? :-)

  -- Dev


Quote:

> Great explanation, Michel!

> I'm going to have steal this for the next time it comes up...

> On Tue, 12 Dec 2000 07:03:20 -0500, "Michel Walsh"

> >Null means not available, not applicable, unknown, etc. A standard
example
> >is "Does the hair color of a bald man (hair color is not
applicable) is
> >equal to the color of his car (he don't own any car)? " so, does
null = null
> >?  the answer is Null (don't know).



Mon, 02 Jun 2003 09:05:20 GMT  
 Null = Null Not Working ?
Hi,

I'll try to make something nice. To Caesar what belong to Caesar, this
example is originally from Joe Celko (the bald man).

(Dev: the French page reference seems broken: from the main page everything
is fine, but in the English version, click on API to get the API section and
then, on that page, click to get the French page.... The home page reference
seems the only working one... or it is just me?)

Vanderghast, Access MVP


Quote:
> Yes Michel.... How about adding this to the FAQ? :-)

>   -- Dev



> > Great explanation, Michel!

> > I'm going to have steal this for the next time it comes up...

> > On Tue, 12 Dec 2000 07:03:20 -0500, "Michel Walsh"

> > >Null means not available, not applicable, unknown, etc. A standard
> example
> > >is "Does the hair color of a bald man (hair color is not
> applicable) is
> > >equal to the color of his car (he don't own any car)? " so, does
> null = null
> > >?  the answer is Null (don't know).



Mon, 02 Jun 2003 20:51:24 GMT  
 Null = Null Not Working ?
Hi,

Please also take note that a JET index with no duplicated values consider
all NULL are different, and it is MS SQL Server with a unique index (NOT a
PrimaryKey since a PK could not be null) that considers all NULL equal, for
the no duplication purpose.... Just one more conversion problem....

Hoping it may help,
Vanderghast, Access MVP


Quote:
> Yes Michel.... How about adding this to the FAQ? :-)

>   -- Dev



> > Great explanation, Michel!

> > I'm going to have steal this for the next time it comes up...

> > On Tue, 12 Dec 2000 07:03:20 -0500, "Michel Walsh"

> > >Null means not available, not applicable, unknown, etc. A standard
> example
> > >is "Does the hair color of a bald man (hair color is not
> applicable) is
> > >equal to the color of his car (he don't own any car)? " so, does
> null = null
> > >?  the answer is Null (don't know).



Mon, 02 Jun 2003 22:53:37 GMT  
 Null = Null Not Working ?
Hi Michel,

On Thu, 14 Dec 2000 07:51:24 -0500, "Michel Walsh"

Quote:

> (Dev: the French page reference seems broken: from the main page everything
> is fine, but in the English version, click on API to get the API section and
> then, on that page, click to get the French page.... The home page reference
> seems the only working one... or it is just me?)

Aargh!  You are right, and thanks very much for noticing it. Looks
like I screwed up in the copy/paste, and since I'm using an Include
file for the menu items, it affects every page.

Unfortunately, mvps.org is in the process of moving to a new server.
I'll upload the fix as soon as I get notification that the changes are
complete.

 Thanks again
  -- Dev



Fri, 06 Jun 2003 23:28:30 GMT  
 
 [ 10 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 not null

6. When is Null not Null

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

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

9. Using Null in WHERE is not working !

10. Null = Null error

11. null in vb and null in sql7

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

 

 
Powered by phpBB® Forum Software