[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MS Access, Databases and dealing with NULLS.

Posted on 2010-11-29
4
Medium Priority
?
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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