Date Data Type and Null Values

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

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.

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

Why do have a value in the reject date before it is rejected?

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?

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.

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.

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

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!

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".

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

Adjusted points from 10 to 30

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

Yes, an update query is the way to go.  Try:

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

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

Oops - I didn't notice my typo.  I left out an equals sign.  Try:

Update BBI SET DateOfBirth = Null WHERE DateOfBirth = #31/12/1899#

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

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

Oops - typos again.  For the QBE grid, use this:

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

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

The most likely reason would be no entry in the criteria.  

Can you show the SQL statement that you executed?

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

diek

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

Comment accepted as answer

Jet,
Thank you.
diek