VBA for Report Filter based on Form Checkboxes

[Kirsti]

Hi,

I have a form with 6 different checkboxes on it. The form also has a command button which opens a report.

I want to create a filter on the Report based on the various combinations of the different checkboxes. I started trying to build an IfThenElse expression on the OpenReport argument, but quickly realised that there are up to 720 different possible combinations.

Does anyone have any experience with this, and if so would you be able to share the syntax used?

Thanks,


Kirsti

[June7]

Are these checkboxes representing 6 different fields or 6 possible values from the same field?

Should not need to code every possible combination. Dynamically build criteria string for only the checkboxes that are selected. Review http://www.allenbrowne.com/ser-62code.html

[Kirsti]

Thanks June,

That has helped some, but I still don't have it working.

My Command Button on my Form opens my Report.
On the ReportOpen procedure, I call a Macro which contains this Function.

----------------------------------------------------------------------------
Option Compare Database
Option Explicit

Public Function FnPWCurrentReport()
Dim strWhere As String

strWhere = ""
If Forms!MainMenu!chkOpex = True Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!OC) = 'O'"
End If
If Forms!MainMenu!chkcapx = True Then
If strWhere = "" Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!OC) = 'C'"
Else
strWhere = strWhere + " AND (Reports!OpenReportPWCurrent_ALL!OC) = 'C'"
End If
End If
If Forms!MainMenu!chkEQOpex = True Then
If strWhere = "" Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!OC) = 'O' AND (Not IsNull(Reports!OpenReportPWCurrent_ALL!j))"
Else
strWhere = strWhere + " AND ((Reports!OpenReportPWCurrent_ALL!OC) = 'O' AND (Not IsNull(Reports!OpenReportPWCurrent_ALL!j)))"
End If
End If
If Forms!MainMenu!chkEQCapx = True Then
If strWhere = "" Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!OC) = 'C' AND (Not IsNull(Reports!OpenReportPWCurrent_ALL!j))"
Else
strWhere = strWhere + " AND ((Reports!OpenReportPWCurrent_ALL!OC) = 'C' AND (Not IsNull(Reports!OpenReportPWCurrent_ALL!j)))"
End If
End If
If Forms!MainMenu!chkOldPlant = True Then
If strWhere = "" Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!Order) Like '5*'"
Else
strWhere = strWhere + " AND (Reports!OpenReportPWCurrent_ALL!Order) Like '5*'"
End If
End If
If Forms!MainMenu!chkNewPlant = True Then
If strWhere = "" Then
strWhere = strWhere + "(Reports!OpenReportPWCurrent_ALL!Order) Like '8*'"
Else
strWhere = strWhere + " AND (Reports!OpenReportPWCurrent_ALL!Order) Like '8*'"
End If
End If


End Function

--------------------------------------------------------------------

But nothing is happening.

Please let me know if you see anything glaringly obvious!

Thanks,
Kirsti

[trevor40]

i guess you are adding the where clause elsware to the open report proccess.
it may be better if you put this code on the same button that opens the report

on click
set your where clause
open the report using the where clause
done
you can also debug the code, see were it stops and post the error details for us to see

[June7]

Part of the issue is applying AND operator to multiple parameters for the same field. I really doubt any record will meet the criteria of:

[OC]="O" AND [OC]="C"

Should some of these checkboxes be mutually exclusive? Should they be in an option group (radio buttons are good for this) or as combobox list so only one can be selected?

[Kirsti]

Thanks for your input.

I finally got it working with this: (you were right about the AND operators June).

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String

strWhere = ""
If Forms!MainMenu!chkOpex = True Then
strWhere = strWhere & "OC = 'O'"
End If
If Forms!MainMenu!chkcapx = True Then
If strWhere = "" Then
strWhere = strWhere & "OC = 'C'"
Else
strWhere = strWhere & " OR OC = 'C'"
End If
End If
If Forms!MainMenu!chkEQOpex = True Then
If strWhere = "" Then
strWhere = strWhere & "OC = 'O' AND j = 'EQ'"
Else
strWhere = strWhere & " AND OC = 'O' AND j = 'EQ'"
End If
End If
If Forms!MainMenu!chkEQCapx = True Then
If strWhere = "" Then
strWhere = strWhere & "OC = 'C' AND j = 'EQ'"
Else
strWhere = strWhere & " OR OC = 'C' AND j = 'EQ'"
End If
End If
If Forms!MainMenu!chkOldPlant = True Then
If strWhere = "" Then
strWhere = strWhere & "Order Like '5*'"
Else
strWhere = strWhere & " AND Order Like '5*'"
End If
End If
If Forms!MainMenu!chkNewPlant = True Then
If strWhere = "" Then
strWhere = strWhere & "Order Like '8*'"
Else
strWhere = strWhere & " OR Order Like '8*'"
End If
End If

DoCmd.OpenReport "OpenReportPWCurrent_ALL", acViewPreview, , strWhere

End Sub

[June7]

This really works?

If users can select any combination of the checkboxes, could end up with the following which will probably not give expected results:

"OC = 'O' OR OC = 'C' AND OC = 'O' AND j = 'EQ' OR OC = 'C' AND j = 'EQ' AND Order Like '5*' OR Order Like '8*'"

Parens are critical when mixing AND and OR operators. Don't think the following expressions will evaluate the same way:

field1 = x OR field1 = y AND field2 = a OR field2 = b

(field1 = x OR field1 = y) AND (field2 = a OR field2 = b)

[Kirsti]

Hi June,

You're right. After further testing it seems it is giving some unexpected results.

Unfortunately none of the checkboxes should be mutually exclusive, so I can't move to radio buttons or a combobox list.

I am having trouble getting the parentheses working properly.

With the current code, "OC = 'O' OR OC = 'C' AND Order Like '5*' gives me a result that includes Order Like '8*' which is not the result I want. Are you able to get me started with how to apply the parentheses to get the result I'm after?

[June7]

For starters, include parens around the AND criteria:

"(OC = 'C' AND j = 'EQ')"

" OR (OC = 'C' AND j = 'EQ')"

" AND (OC = 'O' AND j = 'EQ')"

If user is allowed to select both chkOpex and chkCapx then criteria needs to be enclosed in parens:

"(OC = 'C' OR OC='O')"

Same for the plant criteria.

Allowing this makes no sense, again, the AND operator is conflicting:
"(OC = 'C' OR OC='O') AND (OC = 'O' AND j = 'EQ')"

Get the idea how complicated this gets? I have done something like this only once and it was a royal pain to code.

I value my sanity so you are on your own from here.

[Kirsti]

Thank you so much for your help June.

Taking your lead, I started to code it and you're right of course, it was very complicated and I was loosing my sanity. :-)

So, I took a step back and came up with a workaround.

I added a new field and a WHERE clause onto the original query (((WHERE qryPWCurrent_Report_ALL.copex) = "T") AND (([Forms]![MainMenu]![chkOpex])=-1)) and duplicated the query 6 times to meet the 6 different options of the tickboxes.

Then, joined them back together with a UNION query.

I've created a macro to run all 6 queries and the UNION query on the Report Open property, and although I haven't quite compled testing, it is looking good at this point!

[Kirsti]

Nope, still doesn't work!

LOL, back to the drawing board.

[Kirsti]

Ok - my vba skills weren't up to coding of that complexity, but I found a work around that actually works!

I ended up abandoning the checkboxes and using three different comboboxes instead, with 3 different queries filtering the combobox criteria and a macro on the ReportOpen event to run the queries.

Thanks for your help June.