A97 - what would you do 99 times out of a hundred?

[MLH]

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

[Rick Brandt]

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

No. I never allow ZLS in the first place. If I don't have an entry I want a
Null in the field. If the entry is known to be non-existent I would use "N/A"
or "-" (something that the user can see).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[lylefair]

No. I'd commit suicide.

[MLH]

Here's why I even bother to ask:

Consider dogs being picked up, coming into
the pound w/o tags identifying the owners.
So a shepard is entered into tblAnimals and
the related record in tblOwners - well, there
isn't one.

I can almost guarantee that sometime during
development, I'm gonna wanna write a query
having a field in it something like this...

Owner: strOwnrFName & " " & strOwnrLName

And, of course, when there's no owner record,
(or a partial record with no strOwnrFName or
strOwnrLName) - this inevitably bites me in the
ass later.

[MLH]

On Sun, 23 Oct 2005 13:51:30 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

No. I never allow ZLS in the first place. If I don't have an entry I want a
Null in the field. If the entry is known to be non-existent I would use "N/A"
or "-" (something that the user can see).

Yeah. That's what I've been doing. I was just hoping to circumvent
having to examing those 2 fields for the Null condition prior to doing
something with them - EVERYtime I want to do something - something
like strOwnrFName & " " & strOwnrLName or whatever.

[MLH]

>No. I'd commit suicide.

I tried that. But I didn't have time to draft a proper suicide note
because I was too busy writing crap like this...

Spouse:
IIf(IsNull([SpouseFName],"",[SpouseFName])
IIf(IsNull([SpouseLName],"",[SpouseLName])

.... and worrying about what if first name was null and the last name
was not and vise-versa and avoiding concatenation of a leading
or trailing space should either of those conditions be true ...

Just seeking a little less brainstrain.

[Rick Brandt]

MLH wrote:

No. I'd commit suicide.

I tried that. But I didn't have time to draft a proper suicide note
because I was too busy writing crap like this...

Spouse:
IIf(IsNull([SpouseFName],"",[SpouseFName])
IIf(IsNull([SpouseLName],"",[SpouseLName])

... and worrying about what if first name was null and the last name
was not and vise-versa and avoiding concatenation of a leading
or trailing space should either of those conditions be true ...

Just seeking a little less brainstrain.

Just use Nz().

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[lylefair]

Well, if you're helping DOGS then that makes all the difference in the
world.
I'd suggest you use the NZ function:
NZ(SpouseFName,"Not Known or whatever")

[Bob Quintal]

MLH <CR**@NorthState.net> wrote in
news:ic********************************@4ax.com:

No. I'd commit suicide.

I tried that. But I didn't have time to draft a proper suicide
note because I was too busy writing crap like this...

Spouse:
IIf(IsNull([SpouseFName],"",[SpouseFName])
IIf(IsNull([SpouseLName],"",[SpouseLName])

... and worrying about what if first name was null and the
last name was not and vise-versa and avoiding concatenation of
a leading or trailing space should either of those conditions
be true ...

Just seeking a little less brainstrain.

using the & operator does not propagate nulls, so you can build
the string, and then test for null if you need to test.

dim x as string
x= nz(fname & " " & Lname) 'will work perfectly whether fname is
null or populated, and lname is null or populated.

dim x as variant
x= fname & " " & Lname 'will work perfectly anytime.
--
Bob Quintal

PA is y I've altered my email address.

[MLH]

Ya got a grin out-a-me.

[MLH]

10:4 on the Nz. Thx.

[Arno R]

"MLH" <CR**@NorthState.net> schreef in bericht news:ic********************************@4ax.com...

No. I'd commit suicide.

I tried that. But I didn't have time to draft a proper suicide note
because I was too busy writing crap like this...

Spouse:
IIf(IsNull([SpouseFName],"",[SpouseFName])
IIf(IsNull([SpouseLName],"",[SpouseLName])

... and worrying about what if first name was null and the last name
was not and vise-versa and avoiding concatenation of a leading
or trailing space should either of those conditions be true ...

Ever heard of Trim() ??
Trim([SpouseFName] & " " & [SpouseLNAme])

Arno R

[Steve Jorgensen]

On Sun, 23 Oct 2005 10:25:09 -0400, MLH <CR**@NorthState.net> wrote:

On Sun, 23 Oct 2005 13:51:30 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

No. I never allow ZLS in the first place. If I don't have an entry I want a
Null in the field. If the entry is known to be non-existent I would use "N/A"
or "-" (something that the user can see).

Yeah. That's what I've been doing. I was just hoping to circumvent
having to examing those 2 fields for the Null condition prior to doing
something with them - EVERYtime I want to do something - something
like strOwnrFName & " " & strOwnrLName or whatever.

You don't - the "&" string concatenation operator treats Null as a blank
string.

[Tim Marshall]

MLH wrote:

Ya got a grin out-a-me.

It wasn't meant completely as a joke. From your dog post, it sounds to
me as if you're not clear on the idea of how to deal with calculated
fields that combine values from tables where there could well be the
difficulty you mention. In fact, the example you gave of dogs has
absolutely nothing whatsoever to do with the question in your first post.

The issue in the dog example is of two tables, the tbl_Owners you
mention and, presumeably a doggie found table. To write a calculated
field with the owners first and last name, combined with the doggie
found table, the FIRST thing you should be doing is using a Left or
Right join (ie, in the query builder, have arrowheads connecting from
one table to another).

Then, for your owner's field, the calculation would be something very
simple like:

trim(nz(strOwnrFName,"") & " " & nz(strOwnrLName,""))

This allows for only one of first or last name being completed as well.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Tim Marshall]

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

Based on your example of the dogs, I think you have issues confused
here, ML - see my response to you after Lyle's "Spouse" example.

However, to deal with this particular question, I will depart from
others. I prefer to NOT have any null values in any field (except for
dates, where I haven't figured out exactly a means by which to
substitute an undefined value). My reason for this is based largely on
an interesting cross over discussion from the cd.theory guys a couple of
years ago in which this was discussed with religious ferver... 8)

ANyway, using null values to identify undefined values means that
indexes cannot be used to find the undefined values.

Thus, for a numeric key that is used to join tables together, I prefer a
default value of 0, such as in the case of your dog found table and
owner table - dog found table has a 0 instead of any other number in the
foreign key field for the owner table.

Similarly, I populate undefined or unpopulated text fields with an empty
string. It's my opinion (and I emphasize MY opinion) that the use of
null here is not appropriate.

The explanation by others in this thread who have replied (all of whom I
greatly respect as being heads and shoulders above me in Jet experience,
BTW) describing their use of null for an undefined text value sounds to
me as no different as a zero length string. The difference being a zero
length string is stored in a table's indexes, while a null is not.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MLH]

Yep. But I think I'll go with the Nz recommendations.

Ever heard of Trim() ??
Trim([SpouseFName] & " " & [SpouseLNAme])

Arno R

[MLH]

Maybe so. I just thought I was getting something else.

You don't - the "&" string concatenation operator treats Null as a blank
string.

[MLH]

On Sun, 23 Oct 2005 14:59:44 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

MLH wrote:

Ya got a grin out-a-me.

It wasn't meant completely as a joke.

What - killing himself or helping doggies making all the
difference in the world?
(grin again - that's twice today)

Rest of your point understood. Many thx. My needs arose
from a situation in which I had a parent table and a child table
in a one-to-many r'ship where the parent record might be non-
existent or incomplete.

[MLH]

Good points you make here. I'll have to go back and look
for posts by the cd theory guys. That one escaped me
completely (not unusual, BTW).
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sun, 23 Oct 2005 15:12:27 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

Based on your example of the dogs, I think you have issues confused
here, ML - see my response to you after Lyle's "Spouse" example.

However, to deal with this particular question, I will depart from
others. I prefer to NOT have any null values in any field (except for
dates, where I haven't figured out exactly a means by which to
substitute an undefined value). My reason for this is based largely on
an interesting cross over discussion from the cd.theory guys a couple of
years ago in which this was discussed with religious ferver... 8)

ANyway, using null values to identify undefined values means that
indexes cannot be used to find the undefined values.

Thus, for a numeric key that is used to join tables together, I prefer a
default value of 0, such as in the case of your dog found table and
owner table - dog found table has a 0 instead of any other number in the
foreign key field for the owner table.

Similarly, I populate undefined or unpopulated text fields with an empty
string. It's my opinion (and I emphasize MY opinion) that the use of
null here is not appropriate.

The explanation by others in this thread who have replied (all of whom I
greatly respect as being heads and shoulders above me in Jet experience,
BTW) describing their use of null for an undefined text value sounds to
me as no different as a zero length string. The difference being a zero
length string is stored in a table's indexes, while a null is not.

[MLH]

However, to deal with this particular question, I will depart from
others. I prefer to NOT have any null values in any field (except for
dates, where I haven't figured out exactly a means by which to
substitute an undefined value). My reason for this is based largely on
an interesting cross over discussion from the cd.theory guys a couple of
years ago in which this was discussed with religious ferver...

I found something related to the cdtheory guys... back in 2000. Very
interesting 5-yr old topic. Glad I read it, but too deep for me. As
long as I can get paid for an unnormalized database project, I
consider it successful. To me that's normal enough.

[Craig Alexander Morrison]

> I found something related to the cdtheory guys... back in 2000. Very

interesting 5-yr old topic. Glad I read it, but too deep for me. As
long as I can get paid for an unnormalized database project, I
consider it successful. To me that's normal enough.
PLONK!

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"MLH" <CR**@NorthState.net> wrote in message
news:k4********************************@4ax.com...

[Tim Marshall]

MLH wrote:

Good points you make here. I'll have to go back and look
for posts by the cd theory guys. That one escaped me
completely (not unusual, BTW).

Let me advise though, that I seem to recall most of the Access vets here
rejected the theory guys' stuff, rather similar to the recent crossover
with Mr Celkos and others.

While I agree there are certain methods and approaches that are
sacrosanct in relationl database design, I also think there are many
issues that are purely subjective as opposed to absolute rules. The
mistake some of the db theory guys make is that they take absolute
positions on issues such as that of using null strings as data in fields
as well as the more recent holy war skirmishes on "natural" primary keys
(a practice I totally reject 90% of the time, BTW). Whereas I think
these two issues, as well as others I can't think of, are mostly
designer preferences (though I get impatient and rejectful of people who
use natural primary keys in work I'm associated with - I'm a manager, so
that's my perogative).

So it's important for me that others realize that while I may state my
preferences on the above issues, I'm NOT saying others who do the
opposite are wrong or should be run out of town on a rail...

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:h4********************************@4ax.com:

On Sun, 23 Oct 2005 13:51:30 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

MLH wrote:

You have a table - tblOwners. Two of its fields are
strOwnrFName and strOwnrLName.

Would you make the default value "" (ZLS) most
of the time?

No. I never allow ZLS in the first place. If I don't have an
entry I want a Null in the field. If the entry is known to be
non-existent I would use "N/A" or "-" (something that the user can
see).

Yeah. That's what I've been doing. I was just hoping to circumvent
having to examing those 2 fields for the Null condition prior to
doing something with them - EVERYtime I want to do something -
something like strOwnrFName & " " & strOwnrLName
or whatever.

I never use ZLS's, and consider use of it in tables to be a sign of
novice-level database design.

I don't see the issue with handling Nulls. It's pretty easy.

If you need to assign a value from a field that contains Nulls to a
string variable, you can just concatenate it with a ZLS:

strOwnrFName = Me!OwnrFName & vbNullString

Or, if your problem is with parameters of a UDF, you can use a
variant instead (so that you can use it in a query without needing
to concatenate with a ZLS before passing the value to the UDF), and
then within your function, concatenate with a ZLS for any situations
where you need to store the passed value in a string variable.

You should check Allen Browne's articles on handling Nulls. Theya re
intended for programmers with far less experience than yours, but
they are still quite helpful -- I still occasionally notice things
I'd forgotten or didn't know when I review them each time I cite
thjem here in the newsgroup. The URLs are:

The Query Lost My Records!
http://allenbrowne.com/casu-02.html

Nulls: Do I need them?\
http://allenbrowne.com/casu-11.html

Common Errors with Null
http://allenbrowne.com/casu-12.html

I can remember at one time finding Nulls annoying, but now they
don't bother me at all. I'm not sure what conceptual boundary I
passed over to get from the one state to the other, but I am glad
I'm there!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:ic********************************@4ax.com:

No. I'd commit suicide.

I tried that. But I didn't have time to draft a proper suicide
note because I was too busy writing crap like this...

Spouse:
IIf(IsNull([SpouseFName],"",[SpouseFName])
IIf(IsNull([SpouseLName],"",[SpouseLName])

... and worrying about what if first name was null and the last
name was not and vise-versa and avoiding concatenation of a
leading or trailing space should either of those conditions be
true ...

Just seeking a little less brainstrain.

If all you want to do is concatenate "Lastname, Firstname" use
Trevor Best's trick, using the + concatenation operater to propagate
Nulls along with Mid() (assuming this is in the context of a query):

Mid(("12" + [LastName]) & (", " + [FirstName]), 3)

If LastName is Null, nothing gets concatenated at all.

If it has a value, it ends up as "12Fenton", but the Mid() strips
off the first 2 characters. If the Lastname is Null, you end up with
", David" and the Mid() will then strip off the first two
characters.

It's brilliant and I use it exclusively for this kind of thing since
he pointed out the utility of combining the + operator with Mid().

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:0l********************************@4ax.com:

Yep. But I think I'll go with the Nz recommendations.

Ever heard of Trim() ??
Trim([SpouseFName] & " " & [SpouseLNAme])

If you're doing it in firstname lastname order, using Trim() is far
more efficient than anything else.

If you want "lastname, firstname", then using + concatenation to
propagate the nulls along with Mid() is the most efficient method.

Mid(("12" + [LastName]) & (", " + [FirstName]), 3)

I explained how that works in another reply.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

Then, for your owner's field, the calculation would be something
very simple like:

trim(nz(strOwnrFName,"") & " " & nz(strOwnrLName,""))

This allows for only one of first or last name being completed as
well.

Why bother with the Nz()? If you concatenate:

Null & " " & Null

you end up with a space. You can then pass that to Trim() and end up
with a ZLS, which is fine for display.

Nz() is useful, though, if you want to provide alternative text when
there's nothing there. The Mid() function and the + concatenation
operator could help you out here:

Nz(Mid((" "+[OwnrFName]) & (" "+[OwnrLName]), 2), "Unknown Owner")

Since Mid(Null, 2) returns Null, that means that if both are Null,
you'll end up with the alternative text.

If only one of the two names is filled out, you'll end up with that
name, with no leading space.

If both are not Null, then you end up with the two values separated
by a space.

It requires only one call to Nz() and only one call to Mid(), rather
than two callse to Nz() and a call to Trim().

I'd forgotten that Trim() can accept Null (and pass it through), so
you could also do:

Nz(Trim([OwnrFName] & " " & " " + [OwnrLName]), "Unknown Owner")

The point here is to keep the number of function calls to a minimum.

Of course, Nz(strvariable) is nonsensical code, anyway, since a
string variable can't hold a null. If, on the other hand, one reads
the strVariablenames as representing fieldnames in a query, the Nz()
functions serve no purpose whatsoever, as you'll just end up with
Trim(" ") when both fields are Null, which is exactly what you'd get
if you left out the calls to Nz() as well.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

The explanation by others in this thread who have replied (all of
whom I greatly respect as being heads and shoulders above me in
Jet experience, BTW) describing their use of null for an undefined
text value sounds to me as no different as a zero length string.
The difference being a zero length string is stored in a table's
indexes, while a null is not.

But the indexes still return the Null values very quickly, because
they aren't *in* the index.

In my opiniont, the only reason to avoid Nulls is if you must
include your field in a unique index.

I agree that storing a default value makes a great deal of sense,
but simply setting ALLOW ZLS to True does *not* accomplish that, it
just makes it *possible* to store a ZLS in that field.

So what you're really talking about here is not just a matter of
whether or not you allow ZLS's, as was the original question, but of
whether or not you set a default value. You could have a non-ZLS
default value and disallow ZLS's (which I'd see as a good thing, as
ZLS's are incredibly hard to work with). On the other hand, if you
truly want to distinguish "Not Known" from "Has None" then the ZLS
is a good way to store the latter.

I've never had an application where that was a distinction that was
worth maintaining, but there certainly can be such situations.

But I truly think that absent *that* requirement, it's only UNIQUE
indexes that require a value. Searches for empty values in
non-unique indexes are not going to be speeded up by storing a ZLS
(or any other string).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[MLH]

If all you want to do is concatenate "Lastname, Firstname" use
Trevor Best's trick, using the + concatenation operater to propagate
Nulls along with Mid() (assuming this is in the context of a query):

Mid(("12" + [LastName]) & (", " + [FirstName]), 3)

Nice trick. I did some testing in the immediate window.
If MyVariant is Null and you type this...
?Mid(("12" + MyVariant) & (", " + MyVariant), 3)
it does yield Null.

And this...
?Mid(("12" + "") & (", " + ""), 3) yields ", " (comma space)

And this...
?Mid(("12" + "Harvell") & (", " + MyVariant), 3)
Harvell

And this...
?Mid(("12" + MyVariant) & (", " + "Michael"), 3)
Michael

As long as there is no ZLS in the statement, everything comes
out quite logically.

[MLH]

You should check Allen Browne's articles on handling Nulls. Theya re
intended for programmers with far less experience than yours, but
they are still quite helpful -- I still occasionally notice things
I'd forgotten or didn't know when I review them each time I cite
thjem here in the newsgroup. The URLs are:

The Query Lost My Records!
http://allenbrowne.com/casu-02.html
Nulls: Do I need them?\
http://allenbrowne.com/casu-11.html

Common Errors with Null
http://allenbrowne.com/casu-12.html

Very good articles, every one of them. Thx for
recommending them.

[Tim Marshall]

David W. Fenton wrote:

I agree that storing a default value makes a great deal of sense,
but simply setting ALLOW ZLS to True does *not* accomplish that, it
just makes it *possible* to store a ZLS in that field.

You've made points I agree with, however, there still is a difference
between a ZLS and a null value. At least as far as definitions go. A
null value is not equal to another null value, while a ZLS is equal to
another ZLS. I think that was the point, or at least how I interpreted
it from the Theory guys. It makes sense to me, but you're probably
right in that it makes little difference in retrieval speed.
Nevertheless, it's the route I've taken the past couple of years.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

David W. Fenton wrote:

trim(nz(strOwnrFName,"") & " " & nz(strOwnrLName,""))

Why bother with the Nz()? If you concatenate:

Null & " " & Null

you end up with a space. You can then pass that to Trim() and end up
with a ZLS, which is fine for display.

You're right. I dunno if this would work in Oracle though (my main BE),
where I would write in Oraclese:

trim(nvl(strOwnrFName,'') & ' ' & nvl(strOwnrLName,''))

I'll have to try it.

Does this work with SQL Server back ends?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[MLH]

That's a good point you make, Tim. Really, some things
are subjective. Its OK for one to defend his particular flavor
of subjectivity staunchly - even with fire and brimstone if
desired. But in the end, databases can be 'broke' for a
host of reasons - most of which have absolutely nothing
to do with normalization, cd-theory (and all that implies -
if it implies anthing at all), naming conventions, use 'n
application of null's 'n ZLS's...

Owners and users are pretty much happy with working
databases that aren't broke. And they pay for that, they
really do. They pay for what they want and are happy if
they get it. I've got a few Access 2.0 databases that
haven't broke in 2.5 years. I know personally that on
the inside - they smell. But they don't really smell bad
on the outside. I don't think I'm gonna get any calls
to come back 'n fix anything on the inside anytime soon.

I do like hearing all opinions. Mine are usually heavily
influenced by those.

[Bob Quintal]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

ANyway, using null values to identify undefined values means
that indexes cannot be used to find the undefined values.

Total baloney, unless one is dealing with any unique constraint
index, or manually sets the ignore nulls property to yes..
--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

I agree that storing a default value makes a great deal of
sense, but simply setting ALLOW ZLS to True does *not*
accomplish that, it just makes it *possible* to store a ZLS
in that field.

You've made points I agree with, however, there still is a
difference between a ZLS and a null value. At least as far as
definitions go. A null value is not equal to another null
value, while a ZLS is equal to another ZLS. I think that was
the point, or at least how I interpreted it from the Theory
guys. It makes sense to me, but you're probably right in that
it makes little difference in retrieval speed. Nevertheless,
it's the route I've taken the past couple of years.

if you join two tables on zls allowed fields, you are going to
get back a whole bunch of records that you don't really want.
--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:as********************************@4ax.com:

If all you want to do is concatenate "Lastname, Firstname" use
Trevor Best's trick, using the + concatenation operater to
propagate Nulls along with Mid() (assuming this is in the context
of a query):

Mid(("12" + [LastName]) & (", " + [FirstName]), 3)

Nice trick. I did some testing in the immediate window.
If MyVariant is Null and you type this...
?Mid(("12" + MyVariant) & (", " + MyVariant), 3)
it does yield Null.

And this...
?Mid(("12" + "") & (", " + ""), 3) yields ", " (comma space)

And this...
?Mid(("12" + "Harvell") & (", " + MyVariant), 3)
Harvell

And this...
?Mid(("12" + MyVariant) & (", " + "Michael"), 3)
Michael

As long as there is no ZLS in the statement, everything comes
out quite logically.

Um, well, yes, of course. That's the whole point of the idea, and
the whole reason why you don't store ZLS.

If you stored them, then you'd need to test the length of each
string, and that would be a lot more computationally intensive.
That's one of the reasons why storing ZLS's is deprecated.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

I agree that storing a default value makes a great deal of sense,
but simply setting ALLOW ZLS to True does *not* accomplish that,
it just makes it *possible* to store a ZLS in that field.
You've made points I agree with, however, there still is a
difference between a ZLS and a null value. . . .

I didn't say there wasn't.
. . . At least as far as
definitions go. A null value is not equal to another null value,
while a ZLS is equal to another ZLS. I think that was the point,
or at least how I interpreted it from the Theory guys. It makes
sense to me, but you're probably right in that it makes little
difference in retrieval speed. Nevertheless, it's the route I've
taken the past couple of years.

Of course it's different. But whether or not the difference is
needed in a particular application depends on the way its designed
and what needs to be stored.

For most of the databases I've ever created, there was no
significant difference between "no answer yet given" and "the answer
given was blank/none". When that *was* important (a database for
storing data for a medical study) we used default values and *not*
ZLS or Null, and had distinct values for all possible answers with
UNKNOWN as default and NONE one of the choices, in many cases.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

I agree that storing a default value makes a great deal of
sense, but simply setting ALLOW ZLS to True does *not*
accomplish that, it just makes it *possible* to store a ZLS
in that field.

You've made points I agree with, however, there still is a
difference between a ZLS and a null value. At least as far as
definitions go. A null value is not equal to another null
value, while a ZLS is equal to another ZLS. I think that was
the point, or at least how I interpreted it from the Theory
guys. It makes sense to me, but you're probably right in that
it makes little difference in retrieval speed. Nevertheless,
it's the route I've taken the past couple of years.

if you join two tables on zls allowed fields, you are going to
get back a whole bunch of records that you don't really want.

You also can't put a 0 default value in a foreign key if there is no
record in the parent table with a PK value of 0.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tim Marshall]

Bob Quintal wrote:

if you join two tables on zls allowed fields, you are going to
get back a whole bunch of records that you don't really want.

Joins should always be such that it's a PK/FK type of thing. In which
case a PK should never be allowed a ZLS.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Bob Quintal wrote:

ANyway, using null values to identify undefined values means
that indexes cannot be used to find the undefined values.

Total baloney, unless one is dealing with any unique constraint
index, or manually sets the ignore nulls property to yes..

You can call it baloney all you want - that is the definition of an
index - it doesn't index null values.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

Bob Quintal wrote:

if you join two tables on zls allowed fields, you are going to
get back a whole bunch of records that you don't really want.

Joins should always be such that it's a PK/FK type of thing. In
which case a PK should never be allowed a ZLS.

But someone in the discussion was advocating 0 as default value for
numeric fields, including foreign keys. This is obviously a
problematic suggestion for a number of reasons.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

Bob Quintal wrote:

>>ANyway, using null values to identify undefined values means
that indexes cannot be used to find the undefined values.

Total baloney, unless one is dealing with any unique constraint
index, or manually sets the ignore nulls property to yes..

You can call it baloney all you want - that is the definition of
an index - it doesn't index null values.

That's true, but because it doesn't index them, it means that a NOT
NULL or an IS NULL is very easy to return, since NOT NULL means
return everything in the index, and an IS NULL means return
everything that's *not* in the index.

There is not performance benefit to storing a default value in place
of Null (either ZLS or some other value). Indeed, my bet is that it
is *slower*, because then you're just selecting on a value, and my
guess is that the Null case is actually optimized because of the
fact that it's a special case for an index.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bri]

MLH wrote:

Yeah. That's what I've been doing. I was just hoping to circumvent
having to examing those 2 fields for the Null condition prior to doing
something with them - EVERYtime I want to do something - something
like strOwnrFName & " " & strOwnrLName or whatever.

The + operator creates a Null if any element is a Null. You can use it
in conjunction with the & operator to get what you want. Using your
example, assuming that if there is no first name you don't want the
leading space:

(strOwnrFName + " ") & strOwnrLName

If strOwnrFName is Null then the expression in the brackets becomes Null
(rather than a Space). The Null & strOwnrLName = strOwnrLName

--
Bri

[Bob Quintal]

"David W. Fenton" <dX********@bway.net.invalid> wrote in
news:Xn**********************************@216.196. 97.142:

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:

Bob Quintal wrote:

>>ANyway, using null values to identify undefined values
>>means
that indexes cannot be used to find the undefined values.
Total baloney, unless one is dealing with any unique
constraint index, or manually sets the ignore nulls property
to yes..
You can call it baloney all you want - that is the definition
of an index - it doesn't index null values.

That's true, but because it doesn't index them, it means that
a NOT NULL or an IS NULL is very easy to return, since NOT
NULL means return everything in the index, and an IS NULL
means return everything that's *not* in the index.

I'm pretty sure it does index nulls, at least under some
circumstances. Example, create an index on multiple columns,
some containing nulls. Unless the index is specifically set to
exclude nulls, the order is correct, and fast, Ordering on those
columns without the index, or having an index with allow nulls
set to no produces the same order much more slowly, since the
engine can't use the index. This is true in Jet, and I suspect
in SQL server. .
There is not performance benefit to storing a default value in
place of Null (either ZLS or some other value). Indeed, my bet
is that it is *slower*, because then you're just selecting on
a value, and my guess is that the Null case is actually
optimized because of the fact that it's a special case for an
index.
.

--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

Bri <no*@here.com> wrote in news:4dc7f.287888$1i.6175@pd7tw2no:

MLH wrote:

> Yeah. That's what I've been doing. I was just hoping to
> circumvent
having to examing those 2 fields for the Null condition prior to
doing something with them - EVERYtime I want to do something -
something like strOwnrFName & " " & strOwnrLName
or whatever.

The + operator creates a Null if any element is a Null. You can
use it in conjunction with the & operator to get what you want.
Using your example, assuming that if there is no first name you
don't want the leading space:

(strOwnrFName + " ") & strOwnrLName

If strOwnrFName is Null then the expression in the brackets
becomes Null (rather than a Space). The Null & strOwnrLName =
strOwnrLName

But it will have a trailing space if LastName is null.

That's why Trim(FName & " " & LName) is sufficient.

And, of course, if you're concatenating variables and the names
"strOwnrFName" and "strOwnrLName" have any meaning, they would be
strings and can't possibly contain Nulls, so your solution wouldn't
work, anyway.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bri]

David W. Fenton wrote:

Bri <no*@here.com> wrote in news:4dc7f.287888$1i.6175@pd7tw2no:

The + operator creates a Null if any element is a Null. You can
use it in conjunction with the & operator to get what you want.
Using your example, assuming that if there is no first name you
don't want the leading space:

(strOwnrFName + " ") & strOwnrLName

If strOwnrFName is Null then the expression in the brackets
becomes Null (rather than a Space). The Null & strOwnrLName =
strOwnrLName

But it will have a trailing space if LastName is null.

That's why Trim(FName & " " & LName) is sufficient.

And, of course, if you're concatenating variables and the names
"strOwnrFName" and "strOwnrLName" have any meaning, they would be
strings and can't possibly contain Nulls, so your solution wouldn't
work, anyway.

I was just trying to demonstrate how the + would help with dealing with
Nulls. He implied that there were several different things he was doing
that he was having problems with and that the name concat was just an
example.

I agree that for that specific example that Trim works very nicely. And
yes, I was assuming that we were dealing with fields not variables
otherwise this discussion wouldn't even have come up.

--
Bri