Thread: Type mismatch in date field.

Paul - your suggestions does not correct the problem. The same error occurs. I thought for sure your analysis of the code was correct too.

JB - your suggestions is incorect as well. In fact it produceses two programming error
Programming errors

1. If the user enters only numbers for example 10312010, the error is a type mismatch.
2. If the user enters the date in the following format: October 31, 2010 or enters 10/31/2010 there error is a type mismatch.

In each case we recieve the following error:

rs!FollowupDate = CDate(Me.FollowupDate.Text) < -------Error

Let's simplify the solution by explictly saying what needs to be done. In the "Follow Up date Field" we want the user to have only the following three options:

1. Use the date Picker
2. Enter the date in the following format: 10/31/2010
3. Enter the date in the following format: October 31, 2010

The entire code for the userForm is here:

Option Explicit

Private ClType As String
Private rs As ADODB.Recordset
Const FUTURE        As String = "Closed-Future Prospect"
Const NOBID         As String = "Closed-No Bid"
Const UNSUCCESSFUL  As String = "Closed-Unsuccessful"
Const OppOPEN       As String = "Open"
Private ValidClose  As Boolean
Public Property Let ClosedType(val As String)
    ClType = val
'    UserForm1.Caption = val & " Details"
    Calendar.Visible = True
End Property
Public Property Set Data(val As ADODB.Recordset)
    Set rs = val
    Me.Reason.Text = rs!Reason & ""
    If Not IsNull(rs!FollowupDate) Then
        Me.FollowupDate.Text = rs!FollowupDate
    End If
End Property
Private Sub btnCancel_Click()
    Unload Me
End Sub
Private Sub Calendar_Click()
    UserForm1.Source = ClType
    UserForm1.Show vbModal
End Sub
Private Sub btnOK_Click()

    'OK button, escribo los valores en OPT DATA
    ValidClose = True
    Select Case ClType
        Case FUTURE, NOBID
            If Len(Me.FollowupDate.Text) = 0 Then
                ValidClose = False
                MsgBox "You must set a followup date for this type of Close Action"
            End If
    End Select
    If ValidClose Then
        Application.ScreenUpdating = False
        rs!Status = ClType
        rs!Reason = Me.Reason.Text
        If Me.FollowupDate.Text <> "" Then
            rs!FollowupDate = CDate(Me.FollowupDate.Text) <--------- error
        Else
            rs!FollowupDate = Null
        End If
        
        Unload Me
    End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then ' The user did NOT CANCEL the note
        Select Case ClType
            Case FUTURE, NOBID
                If Len(Me.FollowupDate.Text) = 0 Then
                    ' don't close the form, the user must enter a valid date for this Clos Method
                    Cancel = 1
                    MsgBox "You must set a followup date for this type of Close Action"
                End If
        End Select
    End If
End Sub

Any suggestions here ?

By textbox are you referring to the "Follow Up Date" textbox in which case if the user enters a string of letters - for example "afkjasbghd", it causes causes a type mismatch error.

Note that this userform "intializes" another userform called "userform1" (although I do not think it releveant to correcting the error)see below:

Option Explicit

Dim DataSource As String
Dim DataRow As Integer
Const UNSUCC As String = "Closed-Unsuccessful"
Const FUTURE As String = "Closed-Future Prospect"
Const START As String = "StartDate"
Const COMMIT As String = "CommitDate"
Const NoteDate As String = "NoteDate"
Const NOBID As String = "Closed-No bid"
Const SUCC As String = "Closed-Successful"
Const OppOPEN As String = "Open"
Public Property Let Source(val As String)
    DataSource = val
End Property
Private Sub UserForm_Initialize()
    Calendar1.Value = Sheets(VALIDATIONS).Range("N2").Value
End Sub
Private Sub Calendar1_Click()
    Dim ValidDate As Boolean
    
    Select Case DataSource
        Case FUTURE, UNSUCC, OppOPEN, SUCC
            ValidDate = (Calendar1.Value > Date)
            If Not ValidDate Then
                MsgBox "Please set a Followup Date in the future"
            End If
        Case NOBID
                ValidDate = (Calendar1.Value > Date)
                If Not ValidDate Then
                    MsgBox "You must set a Date in the future, for Closed-No Bid"
                End If
        Case START, COMMIT
            ValidDate = True
            Select Case DataSource
                
                Case START
                    If Calendar1.Value > Date Then
                        ValidDate = False
                        MsgBox "You may not set a Start Date in the Future", vbCritical, "Invalid Opportunity Start Date"
                    End If
                Case COMMIT
                    ValidDate = (Calendar1.Value >= CDate(frmOpportunity.StartDate))
                    If Not ValidDate Then
                        MsgBox "Please set the Commit Date AFTER the Start Date", vbCritical, "InValid Opportunity Commit Date"
                    End If
            End Select
        Case NoteDate
            ValidDate = (Calendar1.Value <= Date)
            If Not ValidDate Then
                MsgBox "You may not date a Note in the Future", vbCritical, "Invalid Note Date"
            End If

    End Select
        
    If ValidDate Then
        Select Case DataSource
            Case START, COMMIT
                Select Case DataSource
                    Case START
                        frmOpportunity.StartDate = CStr(Calendar1.Value)
                    Case COMMIT
                        frmOpportunity.CommitDate = CStr(Calendar1.Value)
                End Select
            
        Case NoteDate
            frmOpportunity.NoteDate = CStr(Calendar1.Value)
        Case FUTURE, NOBID, UNSUCC, OppOPEN, SUCC
            UserForm11.FollowupDate = CStr(Calendar1.Value)
        End Select
    End If
    Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Select Case DataSource
        Case FUTURE
            If Calendar1.Value <= Date Then
                ' A FUTURE Date must be provided for Closed-Future Prospect
                Cancel = 1
                MsgBox "You must set a FollowUp Date in the future for this type of Closure action"
            End If
        Case START
            If Calendar1.Value > Date Then
                ' A FUTURE Date must be provided for Opportunity Start Date
                Cancel = 1
                MsgBox "You must set a Start Date which is not in the Future"
            End If
    End Select
    
End Sub

I am following you guys and as I said in my response to Paul it should work but it results in a type mismatch error:

Private Sub btnOK_Click()

    'OK button, escribo los valores en OPT DATA
    ValidClose = True
    Select Case ClType
        Case FUTURE, NOBID
            If Len(Me.FollowupDate.Text) = 0 Or Not IsDate(Me.FollowupDate.Text) Then <---------Paul's Correction
                ValidClose = False 
                MsgBox "You must set a followup date for this type of Close Action"
            End If
    End Select
    If ValidClose Then
        Application.ScreenUpdating = False
        rs!Status = ClType
        rs!Reason = Me.Reason.Text
        If Me.FollowupDate.Text <> "" Then
            rs!FollowupDate = CDate(Me.FollowupDate.Text)
        Else
            rs!FollowupDate = Null
        End If
        
        Unload Me
    End If
End Sub

None of that works. We are obviously missing something.

Sorry about that, I just tried your suggestion. I have also tried pauls and JB's suggestions as well.

Let's recap what is happening: I have userform called Follow Up. Within that userform another userform (called userform 1, it's a calander-date-picker) is called. When the user enteres a string of text say "agkjbgg" into the "Follow Up Date" textbox a type mismatch error occurs. So the error handling within the code below does not work:

Private Sub btnOK_Click()

    'OK button, escribo los valores en OPT DATA
    ValidClose = True
    Select Case ClType
        Case FUTURE, NOBID
            If Len(Me.FollowupDate.Text) = 0 Then
                ValidClose = False
                MsgBox "You must set a follow up date for this type of Close Action"
            End If

    End Select
    
    If ValidClose Then
        Application.ScreenUpdating = False
        rs!Status = ClType
        rs!Reason = Me.Reason.Text
        If Me.FollowupDate.Text <> "" Then
            rs!FollowupDate = CDate(Me.FollowupDate.Text) <------ Error
        Else
            rs!FollowupDate = Null
        End If
        
        Unload Me
    End If
End Sub

Perhaps the usage of ValidClose as a boolean ?