MS Access, Databases and dealing with NULLS.

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
LVL 12
rgn2121Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"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
 
rgn2121Author Commented:
K...that's what I needed.  I really didn't want to add thos "bogus" entries into the system.  Thanks!
0
 
rgn2121Author Commented:
Great!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.