Filtering a report by controls

[TheDrescher]

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!

 

[Duane Hookom]

"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

 

[TheDrescher]

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.

"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

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!

 

[Duane Hookom]

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

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.

"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

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!

 

[TheDrescher]

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!

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

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.

"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


:

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!