Re: Filtering

Add another control to the form. I would use a combobox that lists the
employee id field and the employee name. So the source for the combobox would
include the IdField and the employee Name sorted in order.

Then you can add to the sub

Private Sub Preview_Click()
On Error GoTo Err_Handler
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "Productivity"
strDateField = "[WorkDate]"
lngView = acViewPreview

If IsDate(Me.StartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.StartDate,
strcJetDate) & ")"
End If

If IsDate(Me.EndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.EndDate
+ 1, strcJetDate) & ")"
End If

If Len(Me.CboxEmployee & "") > 0 THEN
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & " [NameOfEmployeeIDField] = " & Me.CboxEmployee
'If the ID field is text and not numeric then use a line like the following
'strWhere=strWhere & " [NameOfEmployeeIDField] = """ & Me.CboxEmployee & """"

End IF

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
End If
Resume Exit_Handler
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Chris75 via AccessMonster.com wrote:
> Hello,
>
> I currently have an unbound form with 2 unbound textboxes (StartDate and
> EndDate). I also have a command button. This form provides me with a report
> giving me employee productivity. The textboxes and the command button allow
> me to filter the report based on a date range. Now this form as it is works
> like it should. The form is called productivityreport.
>
> I would like to expand this further. I want to be able to see all employees
> for a particular range AND individual employees for a particular range.
>
> Would anyone be able to help me with this?
>
> The current working code for this form is as follows:
>
> Private Sub Preview_Click()
> On Error GoTo Err_Handler
> Dim strReport As String
> Dim strDateField As String
> Dim strWhere As String
> Dim lngView As Long
> Const strcJetDate = "\#mm\/dd\/yyyy\#"
>
> strReport = "Productivity"
> strDateField = "[WorkDate]"
> lngView = acViewPreview
>
> If IsDate(Me.StartDate) Then
> strWhere = "(" & strDateField & " >= " & Format(Me.StartDate,
> strcJetDate) & ")"
> End If
> If IsDate(Me.EndDate) Then
> If strWhere <> vbNullString Then
> strWhere = strWhere & " AND "
> End If
> strWhere = strWhere & "(" & strDateField & " < " & Format(Me.EndDate
> + 1, strcJetDate) & ")"
> End If
>
> If CurrentProject.AllReports(strReport).IsLoaded Then
> DoCmd.Close acReport, strReport
> End If
>
> DoCmd.OpenReport strReport, lngView, , strWhere
>
> Exit_Handler:
> Exit Sub
>
> Err_Handler:
> If Err.Number <> 2501 Then
> MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
> "Cannot open report"
> End If
> Resume Exit_Handler
> End Sub
>
> The above code is based on one provide as an example by Allen Browne.
>
> Thank you for your help.
>