Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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,
0
gtrapp
Asked:
gtrapp
3 Solutions
 
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 MVP, Access and Data Platform)Commented:
"like to insert a NULL. "
Are you talking about *existing* records or new records ?

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
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.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
". 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 MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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