Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle
From: Salad on 13 Mar 2010 01:01
Mat 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
>
If AllowZeroLength is true, "" would be valid.
From: Albert D. Kallal on 13 Mar 2010 06:42
"hbinc" <j.van.gils(a)hccnet.nl> wrote in message
news:80515514-a0a7-4ac3-8414-afb84761737a(a)g10g2000yqh.googlegroups.com...

> 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.
>

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(a)msn.com

From: Albert D. Kallal on 13 Mar 2010 06:51
"Mat" <matthew.kay(a)optusnet.com.au> wrote in message
news:88b91647-cfb3-4073-be49-

> Thanks for that reply. So the option is really asking me to choose
> between null or zero length?
>
> AllowZeroLength = true 'means vbnullstring

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.


> AllowZeroLength = false 'means null

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(a)msn.com

From: Mat on 13 Mar 2010 09:14
Thanks all.
From: David W. Fenton on 13 Mar 2010 14:29
hbinc <j.van.gils(a)hccnet.nl> wrote in
news:80515514-a0a7-4ac3-8414-afb84761737a(a)g10g2000yqh.googlegroups.co
m:

> 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.

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/
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle