I have written a function to check for smalldatetime. The forms use dates returned from the system, using DATE. The user is prompted with a messagebox asking for the date, which is already filled in with the system date. If the user types in a different date, it checks for smalldatetime, but only once. Then I get an error converting data type varchar to smalldatetime. I want the user to be prompted continuously until they enter a proper date. I hope this makes sense, the code is below. If someone would take a look I would be very grateful. Thanks in advance.

code:

Public Function TestSmallDateTime(datValue)

Dim varRetVal As Variant
Dim datDefaultDate As Date
Dim datHighDate As Date
Dim datLowDate As Date
datLowDate = #1/1/1900#
datHighDate = #6/6/2079#

TestSmallDateTime = datValue

If datValue >= datLowDate And datValue <= datHighDate Then
TestSmallDateTime = datValue
Else
If datValue >= datHighDate Then
Do Until datValue >= datLowDate And datValue <= datHighDate
MsgBox "You entered an invalid date!! Must be less than " & DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date"
datValue = InputBox("Deduction Change Effective Date (mm/dd/yyyy)? ", "Deduction Change", Format$(DATE, "mm/dd/yyyy"))
Exit Do
Loop
End If
End If


End Function

Here's one approach that may offer you some ideas...


Private Sub Test()
Debug.Print GetDate
End Sub

Public Function GetDate(Optional dMin As Date = #1/1/1900#, Optional dMax As Date = #6/6/2079#) As Date
Dim resp As String

resp = InputBox( _
"Please enter a date between " & vbCrLf & _
dMin & " and " & dMax & ":", "Get Date Dialog", Date)

If DateIsValid(resp, dMin, dMax) Then
GetDate = CDate(resp)
Else
GetDate = GetDate(dMin, dMax)
End If
End Function

Public Function DateIsValid(sTest As String, min As Date, max As Date) As Boolean
Dim d As Date
If IsDate(sTest) Then
d = CDate(sTest)
If d >= min And d <= max Then
DateIsValid = True
Else
MsgBox sTest & " is out of range"
End If
Else
MsgBox sTest & " is not a date"
End If
End Function

If (assuming I have followed you correctly) the date in the date field is defult and the user changes it, in a record. Why don't you test it when the user closes the form or moves to another record?
Stop the event, tell them they have made a misstake, .setfocus on the date field. And get them to change it.
So the use can either undo the changes they have made or enter the correct date. But they can't close or leave the form until they have done one or the other.

Seems a simpler option to me.

Hope this helps. :)

Thank you both for responding back to me, I have it working flawlessly now! Take care:D