Solved

MS Access, Databases and dealing with NULLS.

Posted on 2010-11-29
4
368 Views
Last Modified: 2012-05-10
Curious what some of the more experienced DB folks do as best practice.  I have some table that contain lookup fields.  Right now, the table that those lookup fields link to only contains valid entries, ie, entries that contain usable data.  Not every field though in my primary table is required.  So instead of having values, right now they are null fields.   Should I add an entry to the linked table that has an empty string or the word 'None".   The linked table is just a list of names, Bldg1, 2, 3, etc.  The user has to enter up to 4 drop off locations, but is only required to have 1.  Should I store "none", "", "?"...?

Just looking for something that other experts typically do...

thanks,
rgn
0
Comment
Question by:rgn2121
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 34236412
"Should I add an entry to the linked table that has an empty string or the word 'None".
No.  Have no fear of Nulls.  Nulls are a beautiful thing.  They mean what they say ... No Information.  An Empty string is essentially data, and certainly "None" is.

And for sure you do not want to user empty strings ... aka Zero Length Strings ... and here is why:
http://allenbrowne.com/bug-09.html

scroll down to Zero Length String

And this is a good read:

http://allenbrowne.com/vba-NothingEmpty.html
0
 
LVL 12

Author Comment

by:rgn2121
ID: 34236421
K...that's what I needed.  I really didn't want to add thos "bogus" entries into the system.  Thanks!
0
 
LVL 12

Author Closing Comment

by:rgn2121
ID: 34236423
Great!
0
 
LVL 75
ID: 34236428
"Should I store "none", "", "?"...?"
Again, no. Bad idea.

There are several built in functions to deal with Nulls.
Nz()   Null To Zero
IsNull (SomeValue)

And worse, and Empty String ("") in a Field 'looks like a Null", but it's not. So, it makes it very difficult to distinguish between Nulls and ZLS's.  It's a very rare instance when a you need Allow Zero Length string set to Yes ... occasionally when dealing with importing data into a local table.


0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now