Inconsistent treatment of Null

[Albert D. Kallal]

If Null = Null returns Null, so should Null & "" = Null & "".
No no no!!!

(Null & "") = (Null & "") beocmes
("") = ("")

The above is true. if you use the + sign, then null + "" = null,

But, null & "zoo" = zoo,

and null & "" = ""

 

[Brendan Reynolds]

What's the point, Michael? I think the way 'classic' VB and VBA handle Null
is correct, but whether I think so or not makes about as much difference as
whether I think it's a good idea for the sun to rise in the morning. That's
the way it is, and there isn't the proverbial snowball's chance of it
changing now.

If you want a language that takes a different approach, try VB.NET or C#.
Microsoft is not going to re-write 'classic' VB and VBA, no matter how much
some people may wish they would.

 

[Michael J. Strickland]

--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
---------------------------------------------------------------

If you set the table field's Allow Zero Length Strings property to
True, and its Required property also to True, it will import a blank
string as a zero length string value. This may be the solution here -
not have any Nulls at all.

John W. Vinson[MVP]

I am not creating this table - I'm using the Import wizard to import a text
file. I have no control
over the field characteristics until after the table has been created and
the data is already imported (as nulls). I cannot create a template table
and import to it because the files I'm importing are of varying structure (#
of fields varies).

 

[Michael J. Strickland]

No. Concatenation is not the same as comparison.

Concatenating a blank string with an unknown does not necessarily produce a
blank string
unless the Null is being coerced to a blank string.

Maybe it should, to be consistent, but it doesn't. Presumably someone,
somewhere, decided that the ability to convert Null to a zero-length
string by concatenation was too convenient to pass up.


An indexed field *may* contain Nulls, but a primary key field may not.

I have several tables with Primary key fields containing nulls so I think
you are wrong
I know if you try to designate a field containing Nulls as a Priamry Key in
the Table Design
window it objects, yet you are allowed to do so in code.

If what you say is true, then these 2 statements should generate a compiler
error ( or at least a runtime error), yet they do not:

indIndex.Primary = True
indIndex.Required = False

If you are converting Nulls to zero-length strings before storing them,
then your index is no longer storing Nulls.

....

True, but I delete this temporary index after I'm done copying records into
my table so Nulls are retained in the fields I used as the source of my
index. Thus duplicates are deleted but Nulls are retained.

--

 

[Dirk Goldgar]

I have several tables with Primary key fields containing nulls so I
think you are wrong
I know if you try to designate a field containing Nulls as a Priamry
Key in the Table Design
window it objects, yet you are allowed to do so in code.

If what you say is true, then these 2 statements should generate a
compiler error ( or at least a runtime error), yet they do not:

indIndex.Primary = True
indIndex.Required = False

If so, that's an interesting bug. I'll have to check it out, but I
won't be able to that until after the weekend.

 

[Tim Ferguson]

If Null = Null returns Null, so should Null & "" = Null & "".

Not true: consult the documentation which is crystal-clear and, as others
have pointed out:

? Null + "something"
Null

? Null & "something"
Something

Best wishes



Tim F

 

[John Vinson]

I am not creating this table - I'm using the Import wizard to import a text
file. I have no control
over the field characteristics until after the table has been created and
the data is already imported (as nulls). I cannot create a template table
and import to it because the files I'm importing are of varying structure (#
of fields varies).

Ouch!

Possibly a multistep process: link to the data table (rather than
importing it); copy the table *structure*, no data, manually or in
code; set the Allow ZLS property on the relevant text fields; append
the data. But you're right, that would be a pain!

John W. Vinson[MVP]

 

[John Vinson]

Oh, I tried it.
IsMeaningless(Null/Infinity,vbAbsurd)
It returns Infinity - 1

"Aleph-null bottles of beer on the wall,
Aleph-null bottles of beer.
Take one down, and pass it around -
aleph-null bottles of beer on the wall!"

John W. Vinson[MVP]

 

[Brendan Reynolds]

I have several tables with Primary key fields containing nulls so I think
you are wrong
I know if you try to designate a field containing Nulls as a Priamry Key
in the Table Design
window it objects, yet you are allowed to do so in code.

If what you say is true, then these 2 statements should generate a
compiler error ( or at least a runtime error), yet they do not:

indIndex.Primary = True
indIndex.Required = False

My tests indicate that if you execute the two lines in this order ...

idx.Primary = True
idx.Required = False

.... you get an index that is not Primary and allows Null. If, on the other
hand, you execute the same two lines in the opposite order ...

' idx.Required = False
' idx.Primary = True

.... you get an index that is Primary and does not allow Null.

It would appear that the second instruction silently over-rides the first.
Here's my test code. Run it once as is, then swap the commented lines and
run it again.

Public Sub TestPKNull()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete ("MyTestTable")
On Error GoTo 0
Set tdf = db.CreateTableDef("MyTestTable")
Set fld = tdf.CreateField("TestText", dbText, 50)
fld.Required = False
tdf.Fields.Append fld
Set fld = tdf.CreateField("TestLong", dbLong)
tdf.Fields.Append fld
db.TableDefs.Append tdf

Set idx = tdf.CreateIndex("MyPK")
Set fld = idx.CreateField("TestText", dbText, 50)
idx.Fields.Append fld

'Results in index that is not Primary and allows Null
'Comment out these lines on second run.
idx.Primary = True
idx.Required = False

' 'Results in index that is Primary and does not allow Null
' 'Uncomment these lines on second run.
' idx.Required = False
' idx.Primary = True

tdf.Indexes.Append idx

Debug.Print idx.Primary
Debug.Print idx.Required

'This will raise an error on the second run.
CurrentDb.Execute "INSERT INTO MyTestTable (TestText, TestLong) VALUES
(NULL, 1)", dbFailOnError

End Sub

 

[Guest]

According to SQL convention, something can only be "IS NULL" or "IS NOT
NULL". Null does not have an "equality" attribute. i.e. null = null

I find there are differences in "" and Null which I think should be the same
thing (i.e. Null).

What are the differences between "" and null? in vba / access/ excel?

i.e any differences between
if rs("columnA").value = null then do something
if isnull(rs("columnA").value) then do something
if isnull(rs("columnA")) then do something
if rs("columnA").value = "" then do something

 

[Dirk Goldgar]

According to SQL convention, something can only be "IS NULL" or "IS
NOT NULL". Null does not have an "equality" attribute. i.e. null =
null

I find there are differences in "" and Null which I think should be
the same thing (i.e. Null).

What are the differences between "" and null? in vba / access/ excel?

i.e any differences between
if rs("columnA").value = null then do something
if isnull(rs("columnA").value) then do something
if isnull(rs("columnA")) then do something
if rs("columnA").value = "" then do something

I have no idea about Excel, or even whether it has the concept of Null
(as opposed to Empty). For VBA and Access, these two are the same:

if isnull(rs("columnA").value) then do something
if isnull(rs("columnA")) then do something

This one:

if rs("columnA").value = null then do something

will never "do something", because nothing is ever equal to Null.

This one:

if rs("columnA").value = "" then do something

will "do something" if rs!columnA is equal to a zero-length string (""),
but not if it is Null.

 

[Michel Walsh]

Hi,


"" is a string with zero character in it.

NULL is data that is unknown (if Boolean), or not available, or not
applicable, and so on.


An empty string is something known, available, and which can make
sense. The concept of an empty string is thus completely different than the
concept of NULL.



Since a Boolean NULL is unknown, the question

Null = Null


is well defined: are two unknown values equal? Maybe, maybe not, only when
we will know BOTH would be able to tell for sure, so the only possible
answer is "I don't know", which is, technically: Null


If Null = Null then


will always be in the "else" case, since Null=Null is something
ELSE than TRUE.




Hoping it may help,
Vanderghast, Access MVP