How to check if a date is valid?

[Guest]

I have a text box where the user enters their birthdate? mm/dd/yyyy is the
format. I would like to check mm if > 12 then dispaly an error, dd if > 31
then display an error, if yyyy > 2000 display a year. How can I do this?

 

[Allen Browne]

If you text box is bound to a Date/Time field, Access will not accept the
value unless it can be understood as a valid date.

If the text box is unboud, set its Format property to "Short Date" or
similar, and again only valid dates are accepted.

You can use the IsDate() function in VBA code to test whether a string can
be understood as a date.

 

[Guest]

Actually I think that I need to check it manually because I am working on a
touchscreen and things are a little different because of this and I need to
display a different error message that is appropriate for the touch screen.
So is there a way to seperate what is entered and check each part of it?

 

[Douglas J Steele]

You can use the string function Left, Mid and Right to extract parts of a
string, and you can use InStr to determine where the slashes are.

For instance, code like:

Function IsMMDDYYYYDate(InputString As String) As Boolean

Dim booFlag As Boolean
Dim intFirstSlash
Dim intSecondSlash
Dim strMM
Dim strDD
Dim strYYYY

booFlag = False

If Len(InputString) = 10 Then
intFirstSlash = InStr(InputString, "/")
If intFirstSlash > 0 Then
intSecondSlash = InStr(intFirstSlash + 1, InputString, "/")
If intSecondSlash > 0 Then
strMM = Left$(InputString, intFirstSlash - 1)
strDD = Mid$(InputString, intFirstSlash + 1, _
IntSecondSlash - intFirstSlash - 1)
strYYYY = Mid$(InputString, intSecondSlash + 1)
booFlag = (Format(DateSerial(CLng(strYYYY), _
CLng(strMM), CLng(strDD), _
"mm\/dd\/yyyy") = InputString)
End If
End If
End If

IsMMDDYYYYDate = booFlag

End Function

 

[Guest]

I am kind of confused with the function you provided could you explain it a
little. I am not seeing any checks if they month is > 12, date >31, and year

 

[Douglas J. Steele]

Unless you want specific error messages (telling them the month is wrong, or
that the day is wrong), there's no real need to check the values of the
individual fields. The DateSerial function will take any value. What I'm
doing is checking to make sure that the date DateSerial returns (formatted
in mm/dd/yyyy format) is the same as what was passed to the function. It
will not be the same if the string that was passed to the function isn't a
valid date.

 

[Guest]

So all I have to do now is replace InputString with me.BirthDate because that
is the text box where it is located. Do I have to do anything with the
InputString in the function call?

 

[Douglas J. Steele]

InputString is a parameter in the function: you don't change anything.

You'd call the function along the lines of:

If IsMMDDYYYYDate(Me.BirthDate) = False Then
MsgBox "The Birthdate is invalid"
End If

 

[Guest]

I am getting an error on this statement:
booFlag = (Format(DateSerial(CLng(strYYYY), CLng(strMM), CLng(strDD),
"mm\/dd\/yyyy") = InputString)

 

[Douglas J. Steele]

Sorry, my fault. I left out a closing parentheses. It should be

booFlag = (Format(DateSerial(CLng(strYYYY), _
CLng(strMM), CLng(strDD)), _
"mm\/dd\/yyyy") = InputString)

 

[Guest]

Thanks, works perfect

Sorry, my fault. I left out a closing parentheses. It should be

booFlag = (Format(DateSerial(CLng(strYYYY), _
CLng(strMM), CLng(strDD)), _
"mm\/dd\/yyyy") = InputString)