Link to home
Start Free TrialLog in
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?
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brothertruffle880

ASKER

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
One more question:
When would someone want to populate a field with zero-length?
:-)

That is wide ope
Wide open to discussion...

 IMO, never... I'd personally rather leave the fields null.
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.
>> 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.
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