Solved

How to add NULLs to an Access Database

Posted on 2011-03-11
10
258 Views
Last Modified: 2012-06-27
Hello,

I have an Access database with some forms with a variety of controls for a variety of data types on it:  Text boxes for alpha characters, numeric data types, date fields, drop down list boxes, and check boxes.

To show that data is unknown, not applicable, or that the data will be added later, I like to insert a NULL. How can I do this with Access 2007?

Thanks,
0
Comment
Question by:gtrapp
[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
10 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35114458
Gtrapp,

At the time you insert a record in a table and even entering only one value of its fields, other fields are defaulted to be nulled.

Let us say you have ID, FirstName, LastName, DOB in your table. When you enter even only the FirstName for that record, ID, LastName and DOB are already null.

Sincerely,
Ed
0
 
LVL 75
ID: 35114497
"like to insert a NULL. "
Are you talking about *existing* records or new records ?

mx
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 35115597
As suggested by mx:

If you're dealing with existing records, you can update a column to a Null value like this:

"UPDATE MyTable SET Col1= NULL"

Note also that 2007 changed the way tables fields are added (during design) to NOT include some defaults - for example, in 2003 and earlier a Numeric field was ALWAYS defaulted to 0. That changed in 2007 - there is no Default value unless you set one.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 75
ID: 35117298
". That changed in 2007 - there is no Default value unless you set one."
It's about time !

Another way at the Form level to add Nulls to Fields in Existing records is using the Form Before Update event, which might end up looking like this:

Private Sub Form_BeforeUpdate (Cancel As Integer)
    Me![Field1] = Null
     Me![YourFieldName] = Null    

End Sub
0
 

Author Comment

by:gtrapp
ID: 35131327
I am talking about new records, and adding records via a form or the datasheet. I looking for like a 3 state for fields. Some lIke:

1. Blank or nothing
2. Actual values (numbers or characters or a date)
3. NULL

How can I do this for numeric, checkbox, date or text columns)?
0
 
LVL 75
ID: 35131355
Blank or Nothing is the same as Null in Access.

mx
0
 

Author Comment

by:gtrapp
ID: 35132760
So, if blank or nothing is the same as NULL in Access, how to avoid putting UNKNW in numeric fields in Access? I could change all of my date and numeric fields to Text, I suppose. Any suggestions?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 334 total points
ID: 35132814
If you define a Field in a Table as Numeric, then the Field size as whatever ... Long, Single, etc ... those fields will in fact be Null until some valid numeric value is entered ... either directly at the table lever or at the form level.

"I could change all of my date and numeric fields to Text, I suppose."
Probably not a good idea.

I'm not really sure what your concern is here ... ?

mx
0
 

Author Comment

by:gtrapp
ID: 35132972
Well, more on the lines of convincing the user that a blank field or field with nothing in it represents UNKNOWN.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 334 total points
ID: 35133064
Well it does ... that's just a fact in Access.  If they choose not to accept that, it should not have to be your problem.  

mx
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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