Null vs empty

[Deborah Mowry]

Wow - how silly.
As posed early today, I asked in a query why I cannot find "Is Null" in a
field that definitely contains no entry. The table was imported from an
external source therefore the problem is somehow linked with the
export/import feature as Is Null works on any other table.

Now...I just did an Edit|Replace in one of the fields that contain empty
contents with the following:
Find What: null
Replace with: Null
then clicked Replace All
It only took 30 sec. and all the empty fields now contain the word "Null".

I am really stumped, I cannot query for Is Null and yet I can Search and
Replace a Null content??!!

Is there a bug somewhere here? a patch I need?

Deborah

 

[Paul Overway]

Your ability to query on a "null" field depends on how the table is set up.
If your table fields allow zero length string, you won't find null entries
because there aren't any...the "empty" fields are a zero length string.
You'd have to query with Len([FieldName])=0. Check the design of your
table. Personally, I prefer not allowing zero length strings....queries are
easier and less confusing for most people.

 

[david epsom dot com dot au]

It looks like you wrote "Null" into each field.
Now you can use
= "Null"

(david)

 

[Paal Andersen]

Hmm sceary
I tried Oracl db and when i inserted "" it replaced it with NULL.....
Bugger.....

Your ability to query on a "null" field depends on how the table is set up.
If your table fields allow zero length string, you won't find null entries
because there aren't any...the "empty" fields are a zero length string.
You'd have to query with Len([FieldName])=0. Check the design of your
table. Personally, I prefer not allowing zero length strings....queries are
easier and less confusing for most people.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

Wow - how silly.
As posed early today, I asked in a query why I cannot find "Is Null" in a
field that definitely contains no entry. The table was imported from an
external source therefore the problem is somehow linked with the
export/import feature as Is Null works on any other table.

Now...I just did an Edit|Replace in one of the fields that contain empty
contents with the following:
Find What: null
Replace with: Null
then clicked Replace All
It only took 30 sec. and all the empty fields now contain the word "Null".

I am really stumped, I cannot query for Is Null and yet I can Search and
Replace a Null content??!!

Is there a bug somewhere here? a patch I need?

Deborah

 

[Anne Nolan]

Oracle does not distinguish between a zero-length string and NULL.

Hmm sceary
I tried Oracl db and when i inserted "" it replaced it with NULL.....
Bugger.....

Your ability to query on a "null" field depends on how the table is set up.
If your table fields allow zero length string, you won't find null entries
because there aren't any...the "empty" fields are a zero length string.
You'd have to query with Len([FieldName])=0. Check the design of your
table. Personally, I prefer not allowing zero length strings....queries are
easier and less confusing for most people.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

Wow - how silly.
As posed early today, I asked in a query why I cannot find "Is Null" in a
field that definitely contains no entry. The table was imported from an
external source therefore the problem is somehow linked with the
export/import feature as Is Null works on any other table.

Now...I just did an Edit|Replace in one of the fields that contain empty
contents with the following:
Find What: null
Replace with: Null
then clicked Replace All
It only took 30 sec. and all the empty fields now contain the word "Null".

I am really stumped, I cannot query for Is Null and yet I can Search and
Replace a Null content??!!

Is there a bug somewhere here? a patch I need?

Deborah

 

[Michel Walsh]

Hi,


I have heard that the ANSI Standard does define a string as a
sequence of "one or more characters", so, should at least have one
character. If so, Jet does not follow the standard and makes a difference
between an empty string ( a string with no character ) and a NULL value
(not available, not applicable, unknown, no match, ... ). And it seems
Oracle does follow the Standard, on this point.


Vanderghast, Access MVP

Hmm sceary
I tried Oracl db and when i inserted "" it replaced it with NULL.....
Bugger.....

Your ability to query on a "null" field depends on how the table is set up.
If your table fields allow zero length string, you won't find null entries
because there aren't any...the "empty" fields are a zero length string.
You'd have to query with Len([FieldName])=0. Check the design of your
table. Personally, I prefer not allowing zero length strings....queries are
easier and less confusing for most people.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

Wow - how silly.
As posed early today, I asked in a query why I cannot find "Is Null"

in

a