How to add NULLs to an Access Database

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,
gtrappAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
 
MINDSUPERBCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"like to insert a NULL. "
Are you talking about *existing* records or new records ?

mx
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
". 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
 
gtrappAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Blank or Nothing is the same as Null in Access.

mx
0
 
gtrappAuthor Commented:
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
 
gtrappAuthor Commented:
Well, more on the lines of convincing the user that a blank field or field with nothing in it represents UNKNOWN.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
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.