Access Open Report with multiple Where parameters

Question

I have two issues in the following code.

(1) How do I get the report to open if I were to select multiple employees. I am successful if I were to select one employee. However, when I select mutiple the Report opens to all employees (including the ones not selected). Note, when I ran this the date parameter was not part of the Open Report.

(2) How can I get the report to pull between the dates and the employees selected? It doesn't seem like what I have below works.

Thanks ahead of time ...

Private Sub cmdOpenRepoprt_Click()

Dim emName As String
Dim varItem As Variant
Dim dtmStart As String
Dim dtmEnd  As String

On Error GoTo cmdOpenReport_Click_error:

'Check to ensure the From and To dates are not empty
If IsNull([StartDate]) And IsNull([EndDate]) Then
    MsgBox "The Start Date and EndDate are empty. Cannot complete request!", vbCritical, "Alert!"
    Exit Sub
Else
    If IsNull([StartDate]) Then
        MsgBox "The Start Date is empty. Cannot complete request!", vbCritical, "Alert!"
        Exit Sub
    Else
        If IsNull([EndDate]) Then
            MsgBox "The End Date is empty. Cannot complete request!", vbCritical, "Alert!"
            Exit Sub
        End If
    End If
End If

dtmStart = Me.StartDate
dtmEnd = Me.EndDate

' If there are no selected items (Contacts) in the list, exit.
If Me!lstContacts.ItemsSelected.Count = 0 Then Exit Sub
           
For Each varItem In Me!lstContacts.ItemsSelected
    ' char(34) adds quotes to Me!lstContacts
    ' Then add a comma to separate the selected items
    emName = emName & Chr(34) & Me!lstContacts.Column(1, varItem) & Chr(34) & " Or "

Next varItem
'removes the last OR with surrounding spaces telling Access to stop looking
emName = Left$(emName, Len(emName) - 4)

DoCmd.OpenReport "METRIC-AOClosedCases", acViewPreview, , "[Employee Name] = " & emName And "[DateClosed] Between dtmStart and dtmEnd", acDialog


cmdOpenReport_Click_error:
    If Err.Number = 2501 Then
        MsgBox "You just cancelled the Report. You'll have to start over.", vbCritical, "Alert!"
    
    ElseIf Err.Number > 0 Then
        MsgBox "error generating report." & vbCrLf & Err.Description, _
            vbCritical, "Report Error"
    End If

End Sub

Answer 1

You should be able to make use of the IN clause. There are several syntax errors in your call to the report. The And should go inside the quotes and you need an additional &. You also want the value of dtmStart & dtmEnd and typically dates need to be surrounded by the # sign. To use IN instead of "[Employee Name] = " & emName, you would use syntax that ended up looking like

ShipRegion In ('Avon','Glos','Som')

Change how you are building emName and remove the Or and replace with a comma. After your loop strip off the trailing comma (instead of the OR). Then your OpenReport line make

DoCmd.OpenReport "METRIC-AOClosedCases", acViewPreview, , "[Employee Name] In (" & emName & ") And [DateClosed] Between #" & dtmStart & "# and #" & dtmEnd & "#", acDialog