inserting a NULL value into a date field

[principessa]

i have a problem in my VB form. it contains a date field. if a try to enter an empty value it gives me an error, smthg like type mismatch!
what should i do? sometimes i have to keep the date field empty?
but i dont know how to deal with null values!

pls help!
thx!

[MagellanTX]

Try something like this....

If iDate = vbNull Then
    txtDate.Text = ""
End If

Hope this helps....

[principessa]

actually i need to do the opposite.... i want to make my db accept null values.
whenever i enter a null value directly from the db, it works.
but if i try to do it throught my form, it doesnt allow me.
so i need smthg like:
if txtdate.value = null then ????? how to oblige my db accept the null value?

hope u understand what i mean

thx

[zuperman]

i recommend changing the field in your database to text, that way u can set its necessary property to false and handle the date validation inside your code...using the isdate function

Dim MyDate, YourDate, NoDate, MyCheck
MyDate = "February 12, 1969": YourDate = #2/12/69#: NoDate = "Hello"
MyCheck = IsDate(MyDate)   ' Returns True.
MyCheck = IsDate(YourDate)   ' Returns True.
MyCheck = IsDate(NoDate)   ' Returns False.

[paulw]

You should really avoid Null if you can.

The easiest way to do this is to have a default value that is "wrong". If you need a date you can over-write it, if not you leave it. You can then update your table with Null by replacing the "wrong" values with a SQL Update query.

BTW Using If x = Null will never work since the result of any operation including Null is always Null so True and False do not apply. You would have to use If IsNull(x) etc.

Cheers,

P.

[Rastro]

I think the problem is not that the DB field doesn't accept Null values but that the value of an empty text box is a zero length string (""), which is a type mismatch with a date field. Wherever your update code is you should try:

If txtDate.Text = "" Then
Recordset!DateField = Null
Else
Recordset!DateField = txtDate.Text
End If

Or:

Recordset!DateField = IIF(txtDate.Text = "",Null,txtDate.Text)

[Jethro]

Try something like

rs!DateField = "" & txtdate

Hope it helps.

[honeybee]

Rastro has the solution, if your date field in the database can accept Null values.

If you have put some table constraint like Not Null on the date field, you cannot insert Null values into this field. In that case, define a date in your application which is out of scope for your application. For e.g. you can use the date 1 January 1901. Call this your basedate. Anytime you find that the user has not entered any date, and the field cannot have a Null value, insert the Base date into the database. While retrieving data, check if any date is equal to the Base date. If yes, you know that the date field is actually supposed to be blank.

That was an elaboration of PaulW's concept, and we are using a similar concept in our application. The essence is to use a dummy date which you will never encounter in the normal working of your application.

.

[Jethro]

Originally posted by honeybee
Rastro has the solution, if your date field in the database can accept Null values.

The essence is to use a dummy date which you will never encounter in the normal working of your application.

.

Hmmm......dummy dates aren't necessarily correct. They implied something happened on a date, whereas the problem would appear to be that in some cases there is no date.

Tried last night on SQL, and it quite happily accepted a null date string, the problem being with vb not processing null strings, (my thoughts are this is a bug in vb, as a lot of other languages accept null values and will process them).

E.g

IN an Inventory file you can have either null or 0 quantity on hand.
0 would indicate the Item has been stocked, but is currently out of stock
Null would indicate the Item has never been stocked.

Try initialising the table field with "" & value, works for SQL 7 and Access 2000.

[honeybee]

There you go.

Paul says No Null Values, and Jethro says No Dummy Dates.

Jethro, I do not think "" will be accepted as a value for a date field by Access. And enforcing Not Null on a column is a common practice for people who want to make doubly sure that only valid data goes into the database.

Your argument about the product stocks having 0 and Null is perfectly valid, but I doubt it will work with a date field.

I guess we have presented enough ways to tackle the situation, let the reader decide which one suites him/her/otherwise.

.