Pulling report with two criteria and date; type mismatch on preview

[Nepenthen]

Hello –

I am still new to Access and I am currently working on debugging some code. I have a form with the following:

Employee Name (combo with select all choice)
Task (combo with select all choice)
Begin date (txt)
End Date (txt)

Both of the combo boxes are pulling from separate union queries

I am trying to get the form to pull a report depending on what criteria is chosen in the form. It works fine if you choose all employees/all tasks, one employee/all tasks, or all employees/one task, but if I try to pull one employee/one task, I get a type mismatch error. (no error code, just a msgbox that states type mismatch). Both of the combo boxes have an ID number(autonumb er) and name (txt) fields. I have used -1 in both of the ID's fields for select all employees/tasks.

Here is my code, please let me know how it looks overall and if there is anything I can improve on. I am still learning :) I am almost certain it has to do with line 43 (after the last else) but I have tried several different ways of wording it and nothing has worked.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
 
    Dim stDocName As String
    Dim strWhereEmpl As String
    Dim strWhereTask As String
 
    If IsNull(Me.cmbEmpl) Then
        MsgBox "Select an employee to Preview."
        Me.cmbEmpl.SetFocus
        Exit Sub
    End If
 
    If IsNull(Me.cmbTask) Then
        MsgBox "Select a task to Preview."
        Me.cmbTask.SetFocus
        Exit Sub
    End If
 
    'check to see that ending date is later than beginning date.
    If IsDate(Begindate) And IsDate(Enddate) Then
        If CDate(Enddate) < CDate(Begindate) Then
            MsgBox "The ending date must be later than the beginning date."
            Enddate.SetFocus
            Exit Sub
        End If
    Else
        MsgBox "Please use a valid date for the beginning date and the ending date values."
        Exit Sub
    End If
 
        strWhereEmpl = "EmplID = " & Forms![QRreportform]!cmbEmpl
        strWhereTask = "TaskID = " & Forms![QRreportform]!cmbTask
        stDocName = "QRsub"
 
    If Me.cmbEmpl = -1 And Me.cmbTask = -1 Then
        DoCmd.OpenReport stDocName, acPreview
    ElseIf Me.cmbTask = -1 Then
        DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
    ElseIf Me.cmbEmpl = -1 Then
        DoCmd.OpenReport stDocName, acPreview, , strWhereTask
    Else
        DoCmd.OpenReport stDocName, acPreview, strWhereEmpl And strWhereTask
    End If
 
 
Exit_Command11_Click:
    Exit Sub
 
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub
 

You guys have already helped me out a lot by just reading your posts. Thanks and thanks again for your help!

Jonathon

[MMcCarthy]

The syntax on your criteria is not correct ..

Try this:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
 
    Dim stDocName As String
    Dim strWhereEmpl As String
    Dim strWhereTask As String
 
    If IsNull(Me.cmbEmpl) Then
        MsgBox "Select an employee to Preview."
        Me.cmbEmpl.SetFocus
        Exit Sub
    End If
 
    If IsNull(Me.cmbTask) Then
        MsgBox "Select a task to Preview."
        Me.cmbTask.SetFocus
        Exit Sub
    End If
 
    'check to see that ending date is later than beginning date.
    If IsDate(Begindate) And IsDate(Enddate) Then
        If CDate(Enddate) < CDate(Begindate) Then
            MsgBox "The ending date must be later than the beginning date."
            Enddate.SetFocus
            Exit Sub
        End If
    Else
        MsgBox "Please use a valid date for the beginning date and the ending date values."
        Exit Sub
    End If
 
        strWhereEmpl = "EmplID = " & Forms![QRreportform]!cmbEmpl
        strWhereTask = "TaskID = " & Forms![QRreportform]!cmbTask
        stDocName = "QRsub"
 
    If Me.cmbEmpl = -1 And Me.cmbTask = -1 Then
        DoCmd.OpenReport stDocName, acPreview
    ElseIf Me.cmbTask = -1 Then
        DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
    ElseIf Me.cmbEmpl = -1 Then
        DoCmd.OpenReport stDocName, acPreview, , strWhereTask
    Else
        DoCmd.OpenReport stDocName, acPreview, strWhereEmpl & " And " & strWhereTask
    End If
 
 
Exit_Command11_Click:
    Exit Sub
 
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub
 

[Nepenthen]

That worked perfectly!! I knew it was something simple but I just couldn't figure it out. Thanks again for your help

[MMcCarthy]

That worked perfectly!! I knew it was something simple but I just couldn't figure it out. Thanks again for your help

You're welcome.