Multifilter for Report
Posted on 2008-10-27
Thank you for your time and patience.
I am setting up a form with cascading combos to act as a filter for queries / reports.
On there form there are a number of cascading combos and four command buttons. Each command button opens a query and a report.
I have set up a similar form in my database where I set the criteria in the query to equal the combo box value using OR LIKE ... IS NULL statements to allow null selections; however the form I am working on at the moment has more combos and I am getting an error message "The query is too complex"
I have come accross this piece of code on EE and was wondering if I could use it. The code as is filters a subform.
Could I set the "strSWC" in the form code, then set the "strSQL" in the on click event of each command button?
How would I modify the code to suite?
Any other suggestions would be much appreciated.
Please note these are not my form or control names this is an example posted on EE.
Dim strSQL as String
Dim strSWC as string 'SQL where clause
If not isnull(Me.cboPN) Then
strSWC = strSWC & "PartnerName='" & Me.cboPM & "' AND "
If Not isnull(Me.cboM) Then
strSWC = strSWC & "Material='" & Me.cboM & "' AND "
' similar for spec no, approved by
'Strip off the final AND Clause
If Not strSWC = vbNulLString Then
strSWC = Left(strSWC,Len(strSWC) - 5)
'Compose the SQL for the subforms's recordsource
strSQL = "SELECT * FROM <your subforms recordsource>"
If Not strSWC = vbNullstring Then
strSQL = strSQL & " WHERE " & strSWC
'Now update the recordsource for subform
'NB where frmSubForm is the name oof the subform object on the main form
Me.frmSubFrom.Form.Recordsource = strSQL