Null Question

[Loukas Marinis]

I define a field as
Text
Required = No
Allow Zero Lenghth = Yes
Indexed Yes(No Duplicates)



I am using visual basic and my question is if there is a to insert as null.
It seems from visual basic this is not possible since you can't declare a
string as null

When i insert "" in this field and move to next field, access delete make it
appearing as a 'real' null. however i can't to it with other record
Anyway to insert a valuew where access will accept it as null

 

[Albert D. Kallal]

I would recommend you don't allow zero length strings.

For all my applications, I assume null for empty fields. The problem with
allowing zero length fields is that then you can have both "" and null in a
field. All of your code will thus have to assume either case..and that is a
pain.

You cannot go

if isnull(me!LastName) = True then
msgbox "please enter the last name"


You will have to use;

if len(nz(me!LastName,"")) > 0 then

However, you most certainly can deal with and use null values in code.

You can declare the variable as Variant, and that does allow you to store
null values

In code you thus can go:

me!LastName = null

or dim vTemp as Variant

vTemp = me!LastName

if isnull(vTemp) = true then
msgbox "yup...is null"

In sql, you can insert nulls as:

CurrentDb.Execute "insert into tblCustomer (LastName,FirstName)
values('Kallal',Null)"

So, you most certainly can deal with null values. However, I would not play
with both zero length strings AND null values. You need to set your designs
in stone at the start of the project. To mix and match null and zero length
is really messy.

for example

select LastName,FirstName with LastName is not null

The problem with allowing zero length strings is that you will have
lastnames that are blank in the above since you allow "" (empty string).
There is so many problems here.

Either assume all empty text fields are null, or assume they are to be
blank..but don't mix the whole mess.

About the only design drawback of always assuming null for empty fields is
that your left joins (and is about 90% of my joins are left) will produce
null fields when child records don't exist, but I never found this to be a
draw back anway.

 

[Rebecca Riordan]

Albert,

Just a note on your comments...there are times when both Null (meaning
"unknown") and "" (meaning "non-existant") make sense. Not often, I'll
grant you, but it does happen. My favorite example is middle names...my
father doesn't have one: "", I don't know my neighbours: Null


--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Mike Painter]

Albert,

Just a note on your comments...there are times when both Null (meaning
"unknown") and "" (meaning "non-existant") make sense. Not often, I'll
grant you, but it does happen. My favorite example is middle names...my
father doesn't have one: "", I don't know my neighbours: Null

While I agree with Rebecca in general, the use of null for middle initials
is rarely a good idea.

NMI is what most agencies use if there is no middle initial.
It saves a *lot* of chewing on sensitive areas of the body and is a really
good example of why Null should normally not be allowed.

Even with the mandating of the initial, NMI or (unk) it is still possible to
arrest the wrong person and that can get expensive for the agency.

 

[Albert D. Kallal]

NMI is what most agencies use if there is no middle initial.

Yes, but for mailing labels, and mail merge stuff, then you kind of have to
take this fact into account. That is a bit of a pain in all form letters and
mailing labels etc to have to "test" for this middle initial.

I guess it depends on how, and what the application is for. As always, ones
mileage does vary on this issue.

 

[Albert D. Kallal]

Albert,

Just a note on your comments...there are times when both Null (meaning
"unknown") and "" (meaning "non-existant") make sense. Not often, I'll
grant you, but it does happen. My favorite example is middle names...my
father doesn't have one: "", I don't know my neighbours: Null

You certainly have come up with a good example! (go figure..and I am not
surprised with such an example coming form you!)

Of course I would probably just add a additional true/false field called
MiddileIUnknown. The reason why I would do this is that when you are looking
at a form, there is no way to tell if the field is zero length, or null.
Further, the access interface does not let the user edit or decide at edit
time if the field is null. So, while you can test this null vs zero length
in a query, you can't deal with it at the UI level very well at all.

Regardless, my only real point is that a design decision needs to be made at
the start of the application. And, if ones does allow both null and zero
length, then that has to be the coding standard.

The real problems I seen with people having trouble with nulls and zero
length strings tends to be the case where NO standard was adopted. Some code
assumes zero length, and other code does not. The only real rule here is
that some assumption has to be made, and then stick to that decision
throughout the application.

 

[Douglas J. Steele]

Aw, gee, Rebecca. Can't you get him one for Christmas? <g>

 

[Fred Boer]

Dear Ms. Riordan:

Your comments about the difference between null and empty fields caught my
eye, and made me think, (once again!), I really *must* get a copy of
"Designing Relational Database Systems"... I have tried a store specializing
in used computer books, and Amazon.com and Chapters.ca, but have come up
empty. I will keep looking...

I suppose I could understand that MSPress might not want to go in for a
whole new printing, but am I correct in assuming that the text of the book
was on the included CD? And if so, couldn't pressure be brought to bear on
MSPress to press some copies of the CD and make *them* available for sale? I
am more than happy to spend the money, even without the actual printed
book..

Just curious, and thanks!
Fred Boer

P.S. Ok, it *might* be my employer's money, if I'm lucky! <g>

 

[Rebecca Riordan]

Pressure has been brought to bear. MS Press is not susceptible. But feel
free to add yours...<g>

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Fred Boer]

I've sent an email. If that doesn't do the trick, perhaps I'll go to their
offices, stand in front of the window and sing show tunes at the top of my
lungs until they accede to my demands...

Cheers!
Fred

P.S. Or show them pictures of my vacation....

 

[Lynn Trapp]

I've sent an email. If that doesn't do the trick, perhaps I'll go to their

offices, stand in front of the window and sing show tunes at the top of my
lungs until they accede to my demands...

Cheers!
Fred

P.S. Or show them pictures of my vacation....

Home videos would work much better. Maybe you and I both can combine our
home videos and really scare them to death...<g>

 

[Fred Boer]

LOL! They don't stand a chance! Hey, Rebecca! Don't worry! The royalty
cheques will start rolling in soon!! <g>

Fred

 

[Lynn Trapp]

hmmmmmmmmm... I wonder if Rebecca will share the royalties for the efforts
we make... <veg>

 

[Joan Wild]

I've sent an email. If that doesn't do the trick, perhaps I'll go to their
offices, stand in front of the window and sing show tunes at the top of my
lungs until they accede to my demands...

Fred, try at amazon.com (not ca). There is one available used. Also have
you tried going to a local Chapters store? Last time I was in mine, there
was one on the shelf.

(note to self - grab it next time)

 

[Fred Boer]

Thanks.. heading over there now...

Fred

Fred, try at amazon.com (not ca). There is one available used. Also have
you tried going to a local Chapters store? Last time I was in mine, there
was one on the shelf.

(note to self - grab it next time)

 

[Lynn Trapp]

Well, shucks... no Chapters store around me.... Gonna have to search Amazon.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm