Hi Art,
Good question.
I almost always leave the report's record source clear of any dynamic filtering. Assuming you have a form with a number of text and combo boxes and a button to open the report. The code to open the report might look like:
Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportNameHere"
strWhere = "1= 1 " 'no effect on the filter
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [MyDateField] >= #" & Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [MyDateField] <= #" & Me.txtEndDate & "# "
End If
If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " AND [MyCityField] = """ & Me.txtCity & """ "
End If
If Not IsNull(Me.txtUnitNumber) Then
strWhere = strWhere & " AND [MyUnitNumberField] = """ & Me.txtUnitNumber & """ "
End If
If Not IsNull(Me.txtSomeNumeric) Then
strWhere = strWhere & " AND [MySomeNumericField] = " & Me.txtSomeNumeric & " "
End If
' add more criteria as needed watching for date, string, and numeric data types
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Jul 2015 12:31:56 -0700
Subject: [MS_AccessPros] Filtering report with mulitple criterias
I am looking for an example of how to filter a report using multiple dropdowns. I have a report which I need to filter by community, move-in date, city, class, project, tenant, unit number. It could be single criteria or some or all of them. If anyone knows of anything, please let me know.
Thank you,
Art Lorenzini
Sioux Falls, SD
Good question.
I almost always leave the report's record source clear of any dynamic filtering. Assuming you have a form with a number of text and combo boxes and a button to open the report. The code to open the report might look like:
Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportNameHere"
strWhere = "1= 1 " 'no effect on the filter
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [MyDateField] >= #" & Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [MyDateField] <= #" & Me.txtEndDate & "# "
End If
If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " AND [MyCityField] = """ & Me.txtCity & """ "
End If
If Not IsNull(Me.txtUnitNumber) Then
strWhere = strWhere & " AND [MyUnitNumberField] = """ & Me.txtUnitNumber & """ "
End If
If Not IsNull(Me.txtSomeNumeric) Then
strWhere = strWhere & " AND [MySomeNumericField] = " & Me.txtSomeNumeric & " "
End If
' add more criteria as needed watching for date, string, and numeric data types
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Jul 2015 12:31:56 -0700
Subject: [MS_AccessPros] Filtering report with mulitple criterias
I am looking for an example of how to filter a report using multiple dropdowns. I have a report which I need to filter by community, move-in date, city, class, project, tenant, unit number. It could be single criteria or some or all of them. If anyone knows of anything, please let me know.
Thank you,
Art Lorenzini
Sioux Falls, SD
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar