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
|
|||
|
|||
Filtering a report by controls
I'm currently running reports from my database for a date range using
controls FromDate and ToDate. This works fine, but I'd like the reports to also sort by an additional control called EmpSelect. The current code I'm using is: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " DoCmd.OpenReport stDocName, acPreview, , strWhere How could I incorporate this new control without buggering up the code I already have? Thanks! |
#2
|
|||
|
|||
Filtering a report by controls
"Sort" suggests applying an order. If you really mean "filter" then it would
help if you provided the data type, what to do if there is no value in the control, and name of the field in the report's record source. Making some WAGs, I would suggest: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " If Not IsNull(Me.EmpSelect) Then strWhere = strWhere & " AND [YourFieldName] =" & Me.EmpSelect & " " 'if the field is text then use this line instead: 'strWhere = strWhere & " AND [YourFieldName] =""" & Me.EmpSelect & """ " End If DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: I'm currently running reports from my database for a date range using controls FromDate and ToDate. This works fine, but I'd like the reports to also sort by an additional control called EmpSelect. The current code I'm using is: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " DoCmd.OpenReport stDocName, acPreview, , strWhere How could I incorporate this new control without buggering up the code I already have? Thanks! |
#3
|
|||
|
|||
Filtering a report by controls
Duane,
Sorry about the confusion, I meant filter additionally by what is selected on the EmpSelect control. This would be a text control displaying the employee name. The field it references is from the EMP table called Full Name. Unfortunately there is no way I can take the space out as I know it causes headaches with coding. If the EmpSelect control is blank, I'd like the report to simply pull every record for the time frame delineated in the FromDate and ToDate controls. "Duane Hookom" wrote: "Sort" suggests applying an order. If you really mean "filter" then it would help if you provided the data type, what to do if there is no value in the control, and name of the field in the report's record source. Making some WAGs, I would suggest: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " If Not IsNull(Me.EmpSelect) Then strWhere = strWhere & " AND [YourFieldName] =" & Me.EmpSelect & " " 'if the field is text then use this line instead: 'strWhere = strWhere & " AND [YourFieldName] =""" & Me.EmpSelect & """ " End If DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: I'm currently running reports from my database for a date range using controls FromDate and ToDate. This works fine, but I'd like the reports to also sort by an additional control called EmpSelect. The current code I'm using is: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " DoCmd.OpenReport stDocName, acPreview, , strWhere How could I incorporate this new control without buggering up the code I already have? Thanks! |
#4
|
|||
|
|||
Filtering a report by controls
So, did my suggested code work if you used the "text" line of code? This
assumes you would substitute your field name. You also need to understand that quite often what is "displaying" in a control might not be the same as the value of the control. This would be particularly true if your control is a list or combo box. -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: Duane, Sorry about the confusion, I meant filter additionally by what is selected on the EmpSelect control. This would be a text control displaying the employee name. The field it references is from the EMP table called Full Name. Unfortunately there is no way I can take the space out as I know it causes headaches with coding. If the EmpSelect control is blank, I'd like the report to simply pull every record for the time frame delineated in the FromDate and ToDate controls. "Duane Hookom" wrote: "Sort" suggests applying an order. If you really mean "filter" then it would help if you provided the data type, what to do if there is no value in the control, and name of the field in the report's record source. Making some WAGs, I would suggest: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " If Not IsNull(Me.EmpSelect) Then strWhere = strWhere & " AND [YourFieldName] =" & Me.EmpSelect & " " 'if the field is text then use this line instead: 'strWhere = strWhere & " AND [YourFieldName] =""" & Me.EmpSelect & """ " End If DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: I'm currently running reports from my database for a date range using controls FromDate and ToDate. This works fine, but I'd like the reports to also sort by an additional control called EmpSelect. The current code I'm using is: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " DoCmd.OpenReport stDocName, acPreview, , strWhere How could I incorporate this new control without buggering up the code I already have? Thanks! |
#5
|
|||
|
|||
Filtering a report by controls
Duane,
Yes, I just got it working. I had to create a text box (EmpNameBox) that populates the text name from the EmpSelect control (which populates an ID number) once I did that, the code works perfectly. Thanks for all your help! "Duane Hookom" wrote: So, did my suggested code work if you used the "text" line of code? This assumes you would substitute your field name. You also need to understand that quite often what is "displaying" in a control might not be the same as the value of the control. This would be particularly true if your control is a list or combo box. -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: Duane, Sorry about the confusion, I meant filter additionally by what is selected on the EmpSelect control. This would be a text control displaying the employee name. The field it references is from the EMP table called Full Name. Unfortunately there is no way I can take the space out as I know it causes headaches with coding. If the EmpSelect control is blank, I'd like the report to simply pull every record for the time frame delineated in the FromDate and ToDate controls. "Duane Hookom" wrote: "Sort" suggests applying an order. If you really mean "filter" then it would help if you provided the data type, what to do if there is no value in the control, and name of the field in the report's record source. Making some WAGs, I would suggest: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " If Not IsNull(Me.EmpSelect) Then strWhere = strWhere & " AND [YourFieldName] =" & Me.EmpSelect & " " 'if the field is text then use this line instead: 'strWhere = strWhere & " AND [YourFieldName] =""" & Me.EmpSelect & """ " End If DoCmd.OpenReport stDocName, acPreview, , strWhere -- Duane Hookom Microsoft Access MVP "TheDrescher" wrote: I'm currently running reports from my database for a date range using controls FromDate and ToDate. This works fine, but I'd like the reports to also sort by an additional control called EmpSelect. The current code I'm using is: Dim strWhere As String Dim stDocName As String stDocName = "FreqByEmployee" strWhere = "[Date] = #" & _ Forms!FreqReportMenu![FromDate] & "# And [Date] =#" & _ Forms!FreqReportMenu![ToDate] & "# " DoCmd.OpenReport stDocName, acPreview, , strWhere How could I incorporate this new control without buggering up the code I already have? Thanks! |
Thread Tools | |
Display Modes | |
|
|