• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Date Data Type and Null Values

Hi,

I have a date field in a ms access data base, to store when a loan was rejected/approved. When I try to store a null value in a VB date data type, it just doesn't work. Essentially, what I want is to remove any date from that field with vb.

diek
Please note that I only have 10 points.
0
diek_nf
Asked:
diek_nf
  • 13
  • 6
  • 3
  • +3
1 Solution
 
PaulHewsCommented:
In your code, instead of assigning null to the date variable, assign it directly to the field when the transaction takes place.  eg.

rsLoanRej("DateTrans") = Null
0
 
jedimikeCommented:
Put a default value of NULL in the table design mode on the table. The only time you should set this field is when it is approved or rejected.  Not before.  
Also make sure that the field accepts NULLs.  Make sure you modify the column for required = NO in the table design.
0
 
diek_nfAuthor Commented:
Mike,
Thank you for helping but I was hoping for a little more discussion, before you locked my question. Firstly, the db is already set that way.

To expect non-programmers to not want to change the date, ie kill it, is not very user friendly. It the business world things change all the time, and  indecision is the key to flexibility( just a joke). As such I cannot be so rigid.

If I store the date in a variant it saves as a null, decimating the reject date, for client x. Paul had a similar idea to just save straight to the db and not use a variable.

diek
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
jedimikeCommented:
Why do have a value in the reject date before it is rejected?
0
 
diek_nfAuthor Commented:
Mike,
Scenario:
client 1 has his loan rejected, eg lack of govt funding, whatever. Client 1 complains to his/her govt representative until the decision is reversed. So there is no longer a reject date. You are somewhat correct it is not always logical, but that is how we humans act know and again.

Is wanting to remove a date by way of vb code that wacky?
0
 
jedimikeCommented:
Unless you keep a history of some sort, it is wacky.  You cant just blow away a date of record.  In that case you mentioned, you should have a reversed column or reopened date or something named similar.  
You have to record somewhere that it was reversed.
0
 
Brendt HessSenior DBACommented:
The easiest way to do what you want to do is to use a Variant variable, rather than a Date variable.

When verifying the date, if the date is blank, put a Null in the Variant variable, otherwise enter the date (be sure to force the text through a conversion, e.g. CVDate(<Text Field>), otherwise it might not be stored in the variant as a Date)

As for the issue of recording changes - thisis a business decision.  Personally, I'd recommend some sort of an action log that records changes to the file, and can have notes entered in against a record (e.g. 'Called client - no answer.')  However, such decisions are always up to the local user's specs.
0
 
diek_nfAuthor Commented:
Yes,
I guess it is wacky. To quote Kramer, i prefer the term "kooky". However, if hypothetically if I did want to do it why should it be that complicated. The reversed date thing sounds sensible.

diek
0
 
VoodoomanCommented:
Hi

I think you will find that date fields dont accept a null value - strange but true!

Usually a nothing date is "00:00:00" in Access once the field has been initialised.

Set the date to "00:00:00" and test for that instead of null!

Good Luck

Voodooman!

0
 
JetScootrCommented:
Experiences with nulls and dates:
First - if this is Access95, have you installed MS' Y2K fix(es)?  That changes things somewhat.  
Second - the Date field CAN store a Null, but the field must be defined in Access to accept nulls.
When we installed the Y2K patches to Access95, we found that date records we had previously set to null suddenly started coming up as 12/31/1899!  This was visible both in Access and off of our data controls, and in reports generated with Crystal reports.
A simple query in Access to Set Date= null when Date=Null fixed this.  We  did find a MS KB article about it, but I don't have the number in front of me.
I have an 12,000 line VB program that routinely sets Date fields to null in Access 95 MDB as a part of record initialization, etc.  I explicitly use the Null constant, i.e., MyTable!MyDateField = Null
Due to quirkiness (being polite here) with allowing the data control to move data directly from text boxes back to database records, our code always intercepts the update in the Validate event, and uses Edit/Update code to actually set the values.  One problem we had repeatedly was empty textboxes, after the user had typed something in and then back-spaced it all out.  To the user, the textbox was blank like it started.  But the "DataChanged" flag was set, so the empty string was written to the field in the database.  Not all field types liked this, and so we simplified our logic by just doing the DB update in our code with the data control's "help".
0
 
diek_nfAuthor Commented:
Jet,
I am certain that you cannot set a date data type to null in vb, unless there is some option I have not discovered.

As a test I assigned dte_StartDate(date data type) = Null, with no saving to the db(Access97), and program comes to a screeching halt.

In my case I use a dtpicker, as opposed to textbox(I have this sorted out from a previous question). Anyways here is my flow --> User Enters Date Via dtpicker cntrl--> I check the contents(changed/checkbox, for dtpicker) to ensure valid data --> dtpicker.value passes test(yes)--> value passed to dte_StartDate--> saved into rs_Fin!d_StartDate.

That 31/12/1899 was driving me a little crazy, mystery solved

I am query illiterate, where in the query would I put "Set Date= null when Date=Null " , my field name is DateOfFunding. Should I follow the procedure for an update query? Thank you.

diek
0
 
diek_nfAuthor Commented:
Adjusted points from 10 to 30
0
 
diek_nfAuthor Commented:
Jet,

I have increased the points, know that I have more to give.

Please do not give up. This null thing is still driving me crazy. I need to know how to creat "A simple query in Access to Set Date= null when Date=Null fixed this.  "

I am query illiterate, where in the query would I put "Set Date= null when Date=Null " , my field name is DateOfFunding. Should I follow the procedure for an update query? Thank you.

diek
0
 
Brendt HessSenior DBACommented:
Yes, an update query is the way to go.  Try:

Update myTableName SET DateOfFunding = Null WHERE DateOfFunding #31/12/1899#
0
 
diek_nfAuthor Commented:
bhess1,
I apologize if this sounds stupid, but I keyed in what you wrote. And I recieved a syntax error. So... I have a query opened in design view
================================================================================
Field: DateOfBirth
Table: BBI
Sort:
Show:
Criteria:
or:

================================================================================

Where should I enter the following:
Update BBI SET DateOfBirth = Null WHERE DateOfBirth #31/12/1899#

Or am I completely in the wrong area?

diek
0
 
Brendt HessSenior DBACommented:
Oops - I didn't notice my typo.  I left out an equals sign.  Try:

Update BBI SET DateOfBirth = Null WHERE DateOfBirth = #31/12/1899#
0
 
diek_nfAuthor Commented:
bhess,
When I run the query I am getting a data type conversion error.

I have null as the default value for DateOfBirth in the table.

================================================================================
Field: DateOfBirth
Table: BBI
Sort:
Update to:"Update BBI SET DateOfBirth = Null WHERE DateOfBirth = #31/12/1899#
"
Criteria:
or:

================================================================================
diek
0
 
Brendt HessSenior DBACommented:
Try This
------------
Field: DateOfBirth
Table: BBI
Sort:
Update to:"Update BBI SET DateOfBirth = Null"
Criteria: "DateOfBirth = #31/12/1899#
"
or:
0
 
Brendt HessSenior DBACommented:
Oops - typos again.  For the QBE grid, use this:

------------
Field: DateOfBirth
Table: BBI
Sort:
Update to: Null
Criteria: = #31/12/1899#

0
 
diek_nfAuthor Commented:
bhess1,
I have been using a copy of the db to play with, and when I executed the update it deleted every date in the DateOfBirth field. Any ideas what caused that. Any help would be appreciated, thank you for everything so far.

diek
0
 
Brendt HessSenior DBACommented:
The most likely reason would be no entry in the criteria.  

Can you show the SQL statement that you executed?
0
 
JetScootrCommented:
OK, sorry it took awhile for this...got sidetracked.  Here ya go:
The probable problem is that data in a database must meet different format rules than data in a text box, that is, what you see AINT what you get.  Remember always that an empty string is different from a null.  "Null" means no data is there of any type; an empty string is a value, of string type, of zero characters.
By ANSI standard SQL, "Null" is compatible with ALL database data types.  By VB standards, Null is NOT compatible with the textbox Text property.
FOr this, I created a database in Access97, "MyDB".  one table, "MyTable", with two fields: "MyField", text, default length and properties; "MyDate", Date/Time, also default length and properties.  I used Access97, not VB code, to create the table.
Using Access97, add three records as follows:
       MyField:   "Record one"         MyDate:   2/2/2000
                  "No Date Here"              (Don't put a value in MyDate)
                  ""                   MyDate:   3/3/2000


In VB, create a new project, with a form.  Put the following on the form:

       cmd_OpenDB    Command button  caption "Open DB"
       cmd_ShowDate  Command buttong caption "Show Date"
       cmd_SetDate   Command button caption "Set Date"
       txt_MyFIeld   Textbox
       txt_MyDate     Textbox

Ok, here's the code:
Option Explicit
Dim MyDB As Database
Dim MyRS As Recordset
'

Private Sub cmd_OpenDB_Click()
Dim DBName As String
DBName = "C:\MyDB.MDB"
Set MyDB = OpenDatabase(DBName, False)
Set MyRS = MyDB.OpenRecordset("MyTable")
MyRS.MoveFirst
End Sub

Private Sub cmd_SetDate_Click()
    MyRS.Edit
    MyRS!MyFIeld = "Setting " & txt_MyField.Text
    If txt_MyDate.Text = "" Then
        MyRS!MyDate = Null
    Else
        MyRS!MyDate = txt_MyDate.Text
    End If
    MyRS.Update
End Sub

Private Sub cmd_ShowDate_Click()
txt_MyField.Text = MyRS!MyFIeld
txt_MyDate.Text = MyRS!MyDate
MyRS.MoveNext
If MyRS.EOF Then MyRS.MoveFirst
End Sub

How it works:
Run the program, and click on "Open DB".  This will open the database and the table, then position the recordset at the first record.

Click on "Show Date" repeatedly, and it will cycle thru the three records.  WHen it runs out of records, it resets to the beginning of the recordset.

Click on "Set Date" and whatever value is in the textboxes will get written to the current record.

Now this program is not going to work in every case.  Here's the reasons:
First, when clicking on show, when the next record has no date value or text value, you will receive a runtime error: "Invalid Use of Null".  This is because Access is returning a Null, but the textbox's Text property insists on getting, at the very least, an empty string.

Next error can happen when clicking "Set Date".  If the data in the txt_MyDate box is not a valid date, you will get an error.  

Another error: If Access97's table design window says "Allow zero length strings? NO", and txt_MyField is empty, then you'll get an error to this effect when trying to write the data.  
Now here's a mind-boggler - start up Access97, blank out the MyField in a record, then move to the next record - it will make the update! What happened? The access97 GUI converted your empty string to a null, and wrote it - nulls don't violate the "no zero-length strings" rule, because they're not strings.

The SQL to update a field is called an "UPDATE" query. Here's an example:

"UPDATE MyTable
Set MyDate = null,
Set MyField = 'Nulled it out,dude'
Where (MyDate= null and MyField='DoIt')"

Anyway, in the VB code above, I show you how to set the date field to null.  Bottom line: Don't rely on the controls on your GUI to use the correct type when writing to your DB.

















0
 
diek_nfAuthor Commented:
JetScootr
Thank you, I have been sidetracked the past few days. I will check out your code later today.

diek
0
 
diek_nfAuthor Commented:
JetScootr,
I haven't tested the code but your explanation of nulls was excellent.

I am in the middle of a new comer error. I took over a project, and trusted the guy I took it over from to properly explain what the clients wanted. So when it was time to present the program to the client they were a tad annoyed. So I am going to spend the next two weeks fixing it.

diek
0
 
diek_nfAuthor Commented:
Comment accepted as answer
0
 
diek_nfAuthor Commented:
Jet,
Thank you.
diek
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 13
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now