Access 2013, Zero Length Strings and Nulls

gkl · Oct 21, 2015

Dear All,

I have a table in Access 2013 with a field that has the Required property set to No and the Allow Zero Length Strings property to Yes.

I am trying to insert an empty ('') value into this field - I tried putting in a white-space, typing some text and then deleting it to no availability. When I run the query

Code:

select * from table_name where field_name is null;

this entry will be reported.

Is there a way of saying I want an empty string for the value rather than null (if you set Required to yes, null is out of the question anyway so there is no problem with this setting)?

Regards,
George

smig · Oct 21, 2015

Empty string "" is not null.

You can check for nulls or empty string using:
Trim(yourfield & "") = ""

plog · Oct 21, 2015

Ahh, the database nerd's paradox--how do you type an empty string? You don't, I think this can only be accompished via code:

Code:

UPDATE table_name SET field_name='';

gemma-the-husky · Oct 21, 2015

it doesn't help, but there is also a constant VbNullString (actually it may be a function)

"UPDATE table_name SET field_name=''& vbnullstring()

gkl · Oct 21, 2015

smig said:
Empty string "" is not null.

You can check for nulls or empty string using:
Trim(yourfield & "") = ""

I know this, please read the question again and you will see it asks nothing about this.

gkl · Oct 21, 2015

plog said:
Ahh, the database nerd's paradox--how do you type an empty string? You don't, I think this can only be accompished via code:

Code:

UPDATE table_name SET field_name='';

I am fine with this, the question is when Required is set to No can you force an empty string value as Access seems to be defaulting it to NULL no matter what you are doing directly from the datasheet view?

gkl · Oct 21, 2015

gemma-the-husky said:
it doesn't help, but there is also a constant VbNullString (actually it may be a function)

"UPDATE table_name SET field_name=''& vbnullstring()

This is true, but I am looking for an answer that doesn't involve VBA; it just works from the datasheet view.

plog · Oct 21, 2015

can you force an empty string value as Access seems to be defaulting it to NULL no matter what you are doing directly from the datasheet view?

I can't parse your question/statement. It started out like a question ('can...'), then it included an observation statement ('Access seems to be...'), then you bolded something for emphasis (which was lost on me) and you finished by punctuating it with a question mark. I don't know what you are asking/stating.

But let me take a guess and give it an answer anyway. Like I said before, its a paradox--how do you type an empty string? You can't, you have to use an UPDATE query.

jdraw · Oct 21, 2015

In my test (Access 2010), a new table with a field (text) that was Required = no
Allow zero length = yes

For three records with values:
-enter a character "a" then backspace
-enter a space
-enter "abc"

first 2 cases are displayed if I

Code:

Select * from tblOct21 where textFldNotRequired Is NULL

Code:

myId	  textFldNotRequired
1	
2

I was not expecting NULL for the record 2 with a single space character??

Also, checking the length of the text field

Code:

SELECT tblOct21.myId, Len([textFldNotRequired]) AS Expr1
FROM tblOct21;

Gives this result (no length for the Null values)

myId Expr1
1
2
3 3

Post posting comment

If I go to the query window -looking at the either query -
and check the Equals Blank filter --- I get records 1 and 2 (the ones that responded positive to the Is NULL)

gemma-the-husky · Oct 21, 2015

yes
the OP is asking whether it is possible to enter a zls using the keyboard. it looks like you can't

jdraw · Oct 21, 2015

Has anyone seen that entering a space into text field will show up as Null value
when using Is NULL in criteria?

I was not expecting this result.
Perhaps a space in a text field is automatically trimmed? But then why not ZLS?

plog · Oct 22, 2015

Has anyone seen that entering a space into text field will show up as Null value
when using Is NULL in criteria?

I was not expecting this result.
Perhaps a space in a text field is automatically trimmed? But then why not ZLS?

Weird. My experiments show that upon data entry a short text field is right trimmed.

a -> Len=1 -> IsNull=False
[space] -> Len=Null -> IsNull=True
a [space] -> Len=1 -> IsNull=False
[space] a [space] -> Len=2 -> IsNull=False

You can verify this when you enter data directly to the table. Leave the field then go back into it and no spaces appear to the right of the last character.

MarkK · Oct 22, 2015

Without testing it, I would expect an Access.Textbox to right trim spaces, and to save "no text" as null. I would not expect, in a datasheet, to be able to save a "" or a " " to a table.

jdraw · Oct 22, 2015

Agreed. That's what my test showed. I thought the space may have been trimmed, and resulted in "" (ZLS). But it is NULL.

gemma-the-husky · Oct 22, 2015

different thought.

can you set the default for the field to a ZLS?

jdraw · Oct 22, 2015

Yes Dave, I just tested it. If you make the default value of the field ="", it does

-not show up criteria Is Null
-does show up when criteria is ""

Access 2013, Zero Length Strings and Nulls

gkl

Registered User.

smig

Registered User.

plog

Banishment Pending

gemma-the-husky

Super Moderator

gkl

Registered User.

gkl

Registered User.

gkl

Registered User.

plog

Banishment Pending

jdraw

Super Moderator

Attachments

gemma-the-husky

Super Moderator

jdraw

Super Moderator

plog

Banishment Pending

MarkK

bit cruncher

jdraw

Super Moderator

gemma-the-husky

Super Moderator

jdraw

Super Moderator

Similar threads

Users who are viewing this thread