IsDate function !!!

[YL99]

Hi,
I've three textboxes and their values are appended to a string variable and then a check is performed by IsDate function

DDText1 = 10 , MMtext2 = 12 ,YYtext3 = 1900

strMydate = DDText1 & "/" & MMtext2 & "/" & YYtext3

IsDate(strMydate)

When the value of MMtext2 is changed to 13 the IsDate() still gives True. In fact it should return False.

Your help would be much appreciated.

 

[MisterC]

Actually, the isdate function returns a true if an expression can be converted to a date.
i.e. 13/12/1900 can be converted to January 12, 1901.

 

[YL99]

Thank you.

Is there a function I can use to check if it's a valid date ?

 

[MisterC]

Ok, hows this:

If Month(strmydate) = MMText2 And _
    Year(strmydate) = YYText3 And _
    Day(strmydate) = DDText1 Then
     'Date is Valid
Else
     'Not Valid
     
End If

Note the lack of error checking etc...

 

[DrJavaJoe]

You should probably do your validation for each text box. For example in the validate of event of your MMText2 only allow values 1 thru 12.

 

[CCLINT]

>i.e. 13/12/1900 can be converted to January 12, 1901.

Not quite true.
The IsDate() will work as the CDate() for this matter, and because there isn't a 13th month, will assume you mean the that 13 is the day, and noth the month.
The next (day) figure will then become the month.

Therefore you cannot expect it to return True for 13/13/1900, thinking it is the 13th of January.
Instead, it will error out...


Also, using
strMydate = DDText1 & "/" & MMtext2 & "/" & YYtext3

is NOT a good idea as the date format can be changed in the control pane region settings and then the user should expect it STILL to work.
If strMydate = "01/06/2003" then are we talking about 6 January, or 1 June?
It depends what the system is set in.
If not in US format, then
?CDate(strMydate)
will then not give the correct results.

Better would be to use:

strMydate = YYtext3 & "-" & MMtext2 & "-" & DDText1

If IsDate(strMydate) Then strMydate = CDate(strMydate)

This will always be intepetated correctly.
Notice the use of CDate(). This will then convert the date from yyyy-MM-dd to the date format set in the control panel.

 

[CCLINT]

Please note that I am assumming that YYtext3 is a 4 digit Year.
If it isn't, then you will need to use a different logic, such as using the DateSerial() function:

1. Use the date validation for each text box as DrJavaJoe stated.

2. strMydate = DateSerial(YYtext3, MMtext2, DDText1)