Very Simple Date Issue

[Guest]

Somebody please help me here. Why does this function NOT work? IsDate
returns false, even though it is a valid date.

Function main()
Dim a, b, c, sDate

sDate = "20011130"
a = Left(sDate, 4)
b = Mid(sDate, 5, 2)
c = Right(sDate, 2)

If IsDate(a & b & c) Then
main = DateSerial(a, b, c)
Else
main = DateSerial(2111, 1, 1)
End If

End Function

Thanks.

 

[John Nurick]

Hi Craig,

In your code, the expression
a & b & c
returns the string
20011130
which is what you started with. Try
a & "-" & b & "-" & c
instead.

 

[David C. Holley]

You need to convert the a & b & c value to a date first. Either by using
DateSerial(a, b, c) in the If..then or using CVDate().

If IsDate(DateSerial(a, b, c))

 

[Douglas J. Steele]

I think John's suggestion is better, David.

Let's assume that sDate is 20053040. While that's not a valid date, your
expression will return True:

?IsDate(DateSerial(2005, 30, 40))
True

That's because DateSerial is too accomodating:

?DateSerial(2005, 30 , 40)
2007-07-10

John's suggestion, however, will reject it:

?IsDate("2005-30-40")
False

 

[Guest]

Thanks for all the help!
--
Craig

I think John's suggestion is better, David.

Let's assume that sDate is 20053040. While that's not a valid date, your
expression will return True:

?IsDate(DateSerial(2005, 30, 40))
True

That's because DateSerial is too accomodating:

?DateSerial(2005, 30 , 40)
2007-07-10

John's suggestion, however, will reject it:

?IsDate("2005-30-40")
False