Allow Zero Length.. yes or no?!

[munkee]

I have just been looking through a few websites and found massive debates around Allow Zero Length strings within a field.

I have read that having the property set to yes can make your queries run a bit slower as they are having to check for nulls and ZLS.

With this being said, when should I be selecting yes and when should I be selecting no?

My database will be for data entry and analysis. I have a number of text fields, 4 memo fields and ofcourse a few number fields within my tables. Should I be setting the ZLS property to no for all my text/memo fields within my database? Is this standard practice?

I am using access 2003.

Thanks,

Chris

[NeoPa]

Allowing ZLSs should be dependent on the applicability of ZLSs only. Let me explain some of the background.

Nulls are used in Access to represent the absence of a value. If you want the value to be optional you set it to Nullable (Required = No). If, on the other hand, you want to reflect a value of en empty string, then set ZLSs to allowed (Allow Zero Length = Yes).

The main thing to remember is that Nulls and ZLSs are entirely different animals and both have their reasons for being.

Frankly, it's hard to think of a real-life scenario where ZLSs would be needed, but say you had a questionnaire sent out to all the attendees of a course. There is an [Attendee] table with fields for the people attending, but also with fields for the answers to each of the questions on the form, including one for the almost mandatory "Please add any further comments you may have about this course".

Some attendees will not return the questionnaire at all. Many will tick most of the boxes, but only one or two will enter any comments. Assuming this data is entered from the printed forms into a form on the database for questionnaire results. Each entry would reflect a returned form. The value for the last field would be a ZLS for those who returned the form, but chose not to add any comments. When these records are used to update the [Attendee] table, those who returned questionnaires with comments would be updated with their comments. Those who returned questionnaires but without comments would be updated too. Only those who failed even to return their questionnaires would still have the default of Null representing their comments, thus indicating the difference between the two.