Solved

Date Data Type and Null Values

Posted on 2000-05-01
26
216 Views
Last Modified: 2010-05-02
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
Comment
Question by:diek_nf
  • 13
  • 6
  • 3
  • +3
26 Comments
 
LVL 38

Expert Comment

by:PaulHews
ID: 2766360
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
 
LVL 2

Expert Comment

by:jedimike
ID: 2766365
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
 

Author Comment

by:diek_nf
ID: 2766440
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
 
LVL 2

Expert Comment

by:jedimike
ID: 2766522
Why do have a value in the reject date before it is rejected?
0
 

Author Comment

by:diek_nf
ID: 2766603
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
 
LVL 2

Expert Comment

by:jedimike
ID: 2766620
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2766764
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
 

Author Comment

by:diek_nf
ID: 2766774
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
 
LVL 5

Expert Comment

by:Voodooman
ID: 2767108
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
 
LVL 2

Expert Comment

by:JetScootr
ID: 2767985
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
 

Author Comment

by:diek_nf
ID: 2769160
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
 

Author Comment

by:diek_nf
ID: 2787996
Adjusted points from 10 to 30
0
 

Author Comment

by:diek_nf
ID: 2787997
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 32

Expert Comment

by:bhess1
ID: 2789135
Yes, an update query is the way to go.  Try:

Update myTableName SET DateOfFunding = Null WHERE DateOfFunding #31/12/1899#
0
 

Author Comment

by:diek_nf
ID: 2789535
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2789870
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
 

Author Comment

by:diek_nf
ID: 2790020
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2790097
Try This
------------
Field: DateOfBirth
Table: BBI
Sort:
Update to:"Update BBI SET DateOfBirth = Null"
Criteria: "DateOfBirth = #31/12/1899#
"
or:
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2790107
Oops - typos again.  For the QBE grid, use this:

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

0
 

Author Comment

by:diek_nf
ID: 2793749
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2794008
The most likely reason would be no entry in the criteria.  

Can you show the SQL statement that you executed?
0
 
LVL 2

Accepted Solution

by:
JetScootr earned 30 total points
ID: 2795057
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
 

Author Comment

by:diek_nf
ID: 2804100
JetScootr
Thank you, I have been sidetracked the past few days. I will check out your code later today.

diek
0
 

Author Comment

by:diek_nf
ID: 2818486
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
 

Author Comment

by:diek_nf
ID: 2844621
Comment accepted as answer
0
 

Author Comment

by:diek_nf
ID: 2844622
Jet,
Thank you.
diek
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now