Allow zero length when creating a new table

[fischadler]

When creating a new table using SQL, how do I set a text field to "allow zero length" in Access?

Thanks!

 

[BNPMike]

I suspect zero length is allowed anyway.

It's a good question. The zero length thing is probably something that only Access enforces (ie through datasheets, forms etc). Jet probably doesn't know what is going on.

 

[fischadler]

When creating a table in Access 2000, through SQL or manually, by default it is set not to allow zero length in text fields. Usually I end up setting that setting for every text field (I find it quite an unuseful setting). In Access XP, however, it is set by default to Accept zero length strings. I don't know how the setting is set when a CREATE TABLE query is run in Access XP though.

I need a way to force Access (whether 2000 or XP) to set that setting to Allow Zero Length when the table is created through SQL.

 

[lupins46]

AFAIK it is not possible to set AllowZeroString using SQL DDL.

You have to use dao or ado to do it.

 

[BNPMike]

Now I agree I should know the answer to this but what's the difference between a null and a zero length string?

 

[cheerio]

NULL effectively means unknown
Zero length means exactly that.

Suppose you have a database table of people you know which has a middle name field. If that field is zero length for me that means I don't have a middle name. If it's null you don't know what my middle name is or have not yet got round to entering it.

 

[BNPMike]

But if you fill in a new record but leave a text field without anything in it - is that a null or a zero length data entry. How does Access or Jet tell the difference?

Is zero length when someone enters ""? What about if the user enters a space?

 

[cheerio]

A space does not get stored. In effect Access throws away trailing spaces. So the user will be storing a zero length string which Access will report as null.

So in Access you cannot distiguish the null string from the zero length string so you will never know whether the gap is because my name is unknown or because I do not have one.

 

[BNPMike]

a zero length string which Access will report as null

Does it think it is null ie will NOT NULL stop empty strings?

 

[cheerio]

correct - in Access NOT NULL stops empty strings

 

[Golom]

In Access XP at least, a CREATE TABLE DDL statement creates a new table with "Allow Zero Length" (i.e. allow Nulls) set to "Yes". I think that's changed from earlier releases (for example Access '97) where the default was "No".

 

[fischadler]

And I can confirm that in Access 2000 the default is also "No". Good to know it has changed in Access XP.