If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reports filtering
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
#2
|
|||
|
|||
Reports filtering
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 "Chris75 via AccessMonster.com" u54211@uwe wrote in message news:9bca2e5061cae@uwe... 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
Thread Tools | |
Display Modes | |
|
|