I generally set this to true for all fields other than fields that I
specify as required.

Considering the default value is false I am wondering if it is a good
or bad practise to set it true?

On Thu, 11 Mar 2010 19:51:55 -0800 (PST), Mat
<matthew.kay (AT) optusnet (DOT) com.au> wrote:

I think it's bad, because it will be difficult to distinguish between
NULL and a ZLS (zero-length string). So you'll often write:
select * from myTable
where myField is null or myfield=""

I prefer to use NULL exclusively.

-Tom.
Microsoft Access MVP

Like Tom, I think a zero-length string is confusing both for the user (who
can't see any difference between a Null and a ZLS and doesn't know how or
when they should enter a ZLS value), and for the developer (since most
databases I've seen don't handle this well.)

The default behavior in Access is not quite the quite you described it, Mat.
The default was false in older versions of Access, but true in more recent
versions. Inconsistent (version specific) is probably the worst possible
outcome.

So, I would encourage you to take Tom's advice and set it to No for all
fields, unless you have a very good, specific reason for needing a ZLS.

So, here's a bit of code that will go through your database and set
AllowZeroLengthString to No for all Text and Memo fields:
http://allenbrowne.com/bug-09.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Tom van Stiphout" <tom7744.no.spam (AT) cox (DOT) net> wrote

On Mar 12, 4:51*am, Mat <matthew.... (AT) optusnet (DOT) com.au> wrote:
Hi Mat,

The availability of NULL-values for dates and numerics is essential.
For text- and memo-fields I have not yet - after 25 years - found any
preference of using NULL-values over zero-length-strings.

In all tables I use NULL-values as default, because Access97 did that.
And ONE way of working (always NULL) is far better than a mixed way of
working.
Recently I changed all my metadata-controls with text-values to zero-
length-string-defaults. In all the string-manipulation it is not
necessary anymore to make exceptions for NULL-values.

So, in due time, if I have very much time, I plan to change to the
other ONE way of working: always zero-length-string in text and memo-
fields.


HBInc.

Thanks for that reply. So the option is really asking me to choose
between null or zero length?

AllowZeroLength = true 'means vbnullstring
AllowZeroLength = false 'means null

Mat wrote:

"hbinc" <j.van.gils (AT) hccnet (DOT) nl> wrote


The problem is at a conceptual level, application level, and even code
level, you still going to have to deal with NULL-values in those text
columns. The reason be, if you take any query that has a master table say
like customers, and then join that to a child table, say like invoice, if
you want that query to return rows, even when there's no child records
(which occurs quite common), then you be using a left join. Thus, when child
records don't exist, those columns will be returned in that query, and they
will all have NULL-values (even if you set the columns in those tables as
zero length). You can't test for zero-length-strings in that column for
invoice, you'll have to test for NULL-values for those customers that don't
have invoices.

The same thing occurs when using dlookup(), or a query to pull data that is
joined data, and the child table doesn't have any rows. So, even those
columns that you set up to be zero length will show up as having null values
in your query and even in your record sets.

What this means is that in all of the above cases, those queries will have
null columns even those setup as zero length columns.

In other words, you're not solving or avoiding having to deal with null
columns. Since that's the case, most of us find it better to adopt a
programming standard that nothing in a column is saved as null. And it
means all over code by adopting this standard it allows us to work with
those nulls that you can't avoid anyway.

Your mileage might vary on the above, and whatever works for you is just
fine. I'm just pointing out that your queries and record sets in your
examples with any joins will still be returning null values and columns.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

"Mat" <matthew.kay (AT) optusnet (DOT) com.au> wrote


The above means it allows nulls, and ALSO allows an empty string. You can
store both in that column, and therefore your code and designs etc. will
have to deal with both settings. So it doesn't just mean an empty string,
it means empty string, and also the allowing of nulls. It would be great if
the setting would say only allow empty strings, but it does not.


The above is correct on your part. You can NOT put a zero-length-string in
there, so you don't have to test for both possibilities in your code. This
is generally why so many of us prefer that setting, but whenever works for
you, is your best choice. I just think it makes more sense to choose null
values here. That means everywhere in your code, you simply test for
something null when you're looking for something that's empty...end of
thinking here.

You could adopt everything being empty as an empty string, but you'd still
be dealing with the possibility in your code that some of the columns and
values will turn up as having null values (and if you read my other post,
you'll see why it's unavoidable). Since we can not avoid having to deal
with nulls then my personal choice is null for nothing in a field.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

Thanks all.

hbinc <j.van.gils (AT) hccnet (DOT) nl> wrote in
news:80515514-a0a7-4ac3-8414-afb84761737a (AT) g10g2000yqh (DOT) googlegroups.co
m:

But you still have to test for ZLS, no? To a human being, a control
with a Null in it is the same as one with a ZLS, so you still have
to deal with it.

Unless the ZLS has a defined meaning distinct from Null, there is
really no reason whatsoever to allow it, in my opinion. Nulls have
nice properties (like propagating themselves in an expression like
Mid(("12"+LastName) & (", "+FirstName), 3)) that ZLS removes.

I try to stamp out ZLS's wherever they occur.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/