Allow Zero Length property 1

[netcert]

I realize when creating a table via the make-table query in Access 97 it creates a default value for the "Allow Zero Length" property of "No" but is there any way to change this default setting?

You can find this setting by going to the design view, clicking on a field and going to the bottom of the window. You'll see it.

Rob

 

[RickSpr]

You can't change it in the Make Table query, nor in code. In fact, I don't think even Access can actually change it in the user interface. If you change it in the user interface, I believe Access creates a new table with the property changed, copies all the data from the old table, deletes the old table, and renames the new table.

Really, though, I have to ask whether you really want to do this. I've never found any advantage in distinguishing between an empty string and Null.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[netcert]

Rick,

Thanks for your reply.

This is one of those rare occasions where all the fields in my table need to be able to allow nulls except for one, ID.

Example:

Table:
ID, first name, last name, company, address1, address2, city, st, zip, country

The data is used for an international mailing list, therefore everyone will have an member ID but not everyone will have a first name, last name or address2 or st or zip or country. Why? If mailing to a university the name would go under "Company" therefore no last name. If international mailing, then it may not hve a zip (some countries don't). If mailing in the U.S., we leave the Country field "Null" or " ".

Make sense?
Rob

 

[RickSpr]

Oh, it makes sense all right--but what you're describing is allowing the fields to be Null, not zero-length strings. To allow them to be Null, you just make sure they're Required = No. And Make Table queries set Required to No in every case, so you should be fine.

Allow Zero Length is for something else. When Allow Zero Length is No, then if you assign "" to the field, Access converts it to Null. When Allow Zero Length is Yes, Access stores the "" instead of Null.

That can be a problem. If you don't type anything into a form control, the associated field is Null. But if you type a space, and then backspace to erase it, you've created a zero-length string. You can't see any difference, but the difference exists in the table. If you then search that field in the table for Nulls, you won't find the row with the zero-length string in the field. Conversely, if you select from the table with a Not Null criterion on that field, you'll get the row with the zero-length string. It can be very confusing.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein