• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • 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...

thanks,
rgn
0
rgn2121
Asked:
rgn2121
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
"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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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