Reports filtering

[Chris75 via AccessMonster.com]

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.

 

[Ed Robichaud]

A much simpler approach would be to use the values from your form as
criteria in a query - e.g. "Between MyForm!StartDate and MyForm!EndDate".

Create a query to result in all employees within the date range. Create
another that returns specific employees. Combine these queries and use that
as the record source of your report.

Nothing wrong with the coded approach, but the query method is easier to
troubleshoot and maintain. BTW, your AND statement does not give two
distinct results; the individual employees (within a date range) will be
already included in all employees (within the same date range).
-Ed