Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle
From: Mat on 11 Mar 2010 22:51
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?
From: Tom van Stiphout on 11 Mar 2010 23:17
On Thu, 11 Mar 2010 19:51:55 -0800 (PST), Mat
<matthew.kay(a)optusnet.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


>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?
From: Allen Browne on 12 Mar 2010 01:00
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(a)cox.net> wrote in message
news:t0gjp5tkoso65nlu2l9t80shoh4vktj32t(a)4ax.com...
> On Thu, 11 Mar 2010 19:51:55 -0800 (PST), Mat
> <matthew.kay(a)optusnet.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
>
>
>>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?

From: hbinc on 12 Mar 2010 15:08
On Mar 12, 4:51 am, Mat <matthew....(a)optusnet.com.au> wrote:
> 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?

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.
From: Mat on 12 Mar 2010 21:18
> 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

 |  Next  |  Last
Pages: 1 2 3
Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle