Null Date Value

[trip85]

I am using the ISNULL function and i need to know what is the acceptable null value of a date so that i can include it in my code for errorchecking. I know that 0 is the null value for integers and currency, does it work for the date too, or is there a diffeent value that must be used.

Please Help!

Ok if you have an integer variable called iNumber and set it to 0, then you are not setting it to NULL, you are setting it to the value 0. O is an acceptable number, NULL is the absence of a value.

Quick example

A Warehouse has records for each product in stock. Product A has a QOH value of 0, this means that there is no stock on hand. If Product A had a QOH value of NULL, that means there has never been stock kept of the product. Pedantic but important distinction in commercial coding.

In code terms, (if not using masked Edit box which has it's own insane rules), you would code something like

   txtQOH = "" & iNumber

This by-passes the "Invalid Use of Null" error message.

Now as to dates, what do you actually mean by a null Date. Do you mean NULL or do you mean a default value. Once again if you are not using the masked edit box then you can code

   txtDate = "" & dDate  ' which can be formated to your requirements

Hope that helps you out a bit, post what you need the "NULL" date to represent, the possible absence of a date, or a default date.

[trip85]

Thanks for responding Jethro.

I need the null date to be an absence of a date value. I have tried CDate("") function as a possible solution to my problem, but is there a better way to accomplish this?

[Dougz999]

If you are simply checking the date is entered then youcan use len(date)

If you want to check that it is a valid date this is as bit more complicated. Withe the Enterprise edition of VB 6 you get a date and time input box.

If you dont have this version just try


if you need to assign null to the date just use

VarDate = Null

Where are you reading the date from, A database or user entry?

[trip85]

I am using VB6 Enterprise. The date is going to be brought in from a database. If the date field of the database of a record is null i don't won't the program to stop I want it to return "00/00/0000 12:00am" for the DATETIME stamp and keep reading through the rest of the records.


Thanks for your help

[Dougz999]

Well Why didnt You say So! : )

All you need to do is (assuming you are using ADO)

once you have opened your recordset (in this Case RS)

If isnull(RS!DateFieldName) then
ReturnedDate = "00/00/0000 12:00am"
else
ReturnedDate = RS!DateFieldName
end if

the coding is virtually identical if your not using ADO, It just seems to be unusual for peopleto use the bang ! notation.

The ! simply allows you to specify a field in the recordset.

you can also use RS.Fields("DatefieldName")

Hope that helps man!

[trip85]

That is exactly what i was looking for!
Thanks for all of your help.

Trip85

Look up the Convert function to cut down on your code, this allows date conversion to basically anything you want.