Avatar of brothertruffle880
brothertruffle880Flag for United States of America asked on

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?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Armen Stein - Microsoft Access MVP since 2006

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kelvin Sparks

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
brothertruffle880

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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
brothertruffle880

One more question:
When would someone want to populate a field with zero-length?
mbizup

:-)

That is wide ope
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
mbizup

Wide open to discussion...

 IMO, never... I'd personally rather leave the fields null.
mbizup

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.
Scott Pletcher

>> 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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Armen Stein - Microsoft Access MVP since 2006

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