Access 2010 - Table Field properties - Allow zero length string questions

There is a setting in field properties to "Allow Zero Length String"
Questions about this:
1.  How does one enter a zero length string into a field?  
 a) Press the space bar? b) Data imported from a text file containing no data in that field?
OR
2.  How do you input a zero length string into a field.
3.  Does leaving a field empty mean that field has a null value?
brothertruffle880Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
A zero length value is not a NULL value - they are quite different.

Pressing the space bar enters a space - data is 1 character long - this is not a zero length either.

Compare

UPDATE Mytable
SET MyField = NULL

Will populate the MyField field in MyTable with NULLs

UPDATE MyTable
SET MyField = ''     -- this is two single quotes

Will populate the MyField field in MyTable with zero length data

If you think your data has a mixture of NUlls and zero length data you'd have to use something like

SELECT *
FROM MyTable
WHERE MyField IS NULL or MyField = ''


Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
In addition to Kelvin's comments,

<< Does leaving a field empty mean that field has a null value? >>

Generally, yes -it will have a null value.  Exception being if you are entering a new record and a given field has a non-null default value.

Otherwise, the only way to get a Zero Length String in your table data is through action queries such as the UPDATE query Kelvin has posted, or string manipulation functions (Left, Right, Mid, Trim, Replace, etc)  in queries as might be used when correcting data.  

On a side note, such action queries as far as I know are also the only way to get "trailing spaces" in your table data.
0
brothertruffle880Author Commented:
mbizup:

Otherwise, the only way to get a Zero Length String in your table data is through action queries such as the UPDATE query Kelvin has posted, or string manipulation functions (Left, Right, Mid, Trim, Replace, etc)  in queries as might be used when correcting data.  


If I have a record with 10 fields, seven of those fields are updated with data changes  (three of the fields are empty and were never populated with data)
I perform an UPDATE query.
Does that mean the three empty fields now have zero-length strings, even though they were never populated with data and were not updated with this query?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mbizupCommented:
No.

Running an update query in itself will not affect those fields...

Unless you explicitly modify them in your SET clause.
0
brothertruffle880Author Commented:
One more question:
When would someone want to populate a field with zero-length?
0
mbizupCommented:
:-)

That is wide ope
0
mbizupCommented:
Wide open to discussion...

 IMO, never... I'd personally rather leave the fields null.
0
mbizupCommented:
One case however where it may be a little tricky to avoid however is Excel imports to Access.  Blanks in Excel are empty strings, not nulls.
0
Scott PletcherSenior DBACommented:
>> Pressing the space bar enters a space - data is 1 character long - this is not a zero length either. <<

Actually that depends.  In many DBMS's, trailing space(s) in a character fields are automatically removed.  A single space would a "trailing" space, so it might be removed and the string set to a zero length value.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
Zero Length Strings can be confusing.  We rarely allow them in our databases, unless there's a specific reason like handling imported data as mentioned above.

Just in case we encounter them, though, we check for unspecified (empty or null) values like this:

If MyField & "" = "" Then

This syntax is pretty short and easy to read once you get used to it, and will be true whether the field is Null or contains an empty string.

Cheers,
Armen Stein
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.