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.

 

[John Spencer]

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

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.

 

[Chris75 via AccessMonster.com]

Hi John,

I added the code you suggested. For whatever reason, I am now unable to open
that form whatsoever. I get an error message about insufficient memory. It
is only that form that is affected. All others are fine.

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

Hello,

[quoted text clipped - 55 lines]

Thank you for your help.

 

[Chris75 via AccessMonster.com]

Hi John,

I got it to work.

Thank you for your help!

Hi John,

I added the code you suggested. For whatever reason, I am now unable to open
that form whatsoever. I get an error message about insufficient memory. It
is only that form that is affected. All others are fine.

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

[quoted text clipped - 64 lines]