• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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...

  • 2
  • 2
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)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:

scroll down to Zero Length String

And this is a good read:

rgn2121Author Commented:
K...that's what I needed.  I really didn't want to add thos "bogus" entries into the system.  Thanks!
rgn2121Author Commented:
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.

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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now