IsDate function doesn't work like you think

[trapper]

I've run across this problem and I want to see if anyone else has as well.

It appears as though the IsDate function works as a IsTime function as well. During my testing, however, I have discovered a major problem with this.

I enter "10:50" in my text box. This is a valid time, but an invalid date. This has the effect of, when I reformat the entered date, of giving me a date/time of "December 30, 1899 10:50:00." 12/30/1899 is the default date if none is given in the Date/time datatype.

Here's a code snippet:

        If IsDate(sText) Then
            WorkDate = CDate(sText)
            txtTextBox.Text = Format (WorkDate, "mm/dd/yyyy hh:mm:ss")
        Else
            MsgBox "Not a valid date"
        End If

Has anyone encountered this problem before? Is there a VB function to *just* check if a string is a valid date?

Thanks,
trapper

[YoungBuck]

Is your sText variable of type Variant? According to the vb help file it needs to be.

[trapper]

YB, I don't think you understand what a variant is.

Look up the VB help file on variant types.

All a variant means is that it can accept all data types and then does a conversion on it. So it could accept a numeric, string, or a float. It can also accept an object, although I don't see how an object could be evaluated as a date.

Try it with a variant or a string. The result would be the same.

[YoungBuck]

I understand what a variant just thought that if you were passing a variable to a function and it excpected a variable of type variant that it had to explicitly declared as type variant (until now ). I imagine I didn't understand you're question the first time. Anyway I looked through and couldn't find any functions that would just check for just date values. I imagine you could write your own IsTime function and use it in conjunction with the IsDate function to keep users from entering the wrong data.

If (IsDate(stext)) And (IsTime(stext) = False) Then

Good Luck!

[trapper]

Anyone else have any thoughts on this?

[trapper]

If anyone is interested, I've found an adequate solution to this.

A Date type is actually represented as a float. The date is stored as the whole number, while the time is stored as the decimal. The date is analogous to the number of days since December 30, 1899. So 0.0 would be midnight on Dec 30, 1899, 1.0 would be midnight Dec 31, 1899...and so on (negative numbers would continue down to Jan 1, 100).

So my solution is to check the Date variable for being greater than zero. If it is less than or equal to zero, then I have a time, but no date (unless the user really did enter December 30, 1899) and I can say that the input is an invalid date.

Thoughts? Ideas? Anyone who actually reads this thread besides me? :-)

trapper

[YoungBuck]

The only way I can see that this wouldn't work is if the user actually entered 12/31/1899, but that seems very unlikely.

[trapper]

YB, thanks for your thoughtful reply, but I had actually already pointed that out. Also, 12/31/1899 would be greater than 0 since it is one day after 12/30/1899.

[jmcswain]

As you pointed out, trapper, the date is the whole number part and the time is the decimal. So you can use the Int() function to strip out the Time part of it.

Basically, first you want to use VarType() to check the subtype of the variable, and return nothing unless it's a DateTime type. If it is of DateTime type, check to see if it is equal to the result of Int()ing it. If it is, it is a valid date with no time. (Well, 0 is actually midnight, but you get the idea.)