combining filters from 2 Multi-Select list boxes

[judytjones via AccessMonster.com]

I have been trying to adapt the Multi-Select list box code (shown by Allen
Browne) to include 2 list box selections. Allen's code loops through the
items selected in an extended listbox to build a wherecondition for the
OpenReport cmd.

I added a second loop for my second listbox, and strung the 2 conditions into
1.

If I set a break point at the openReport line, which is coded as follows

DoCmd.OpenReport strDoc, acViewPreview, wherecondition:=strWhere & " AND " &
strWhere2, OpenArgs:=strDescrip & " " & strDescrip2

the immediate window shows my variables as follows
? strwhere
[DeptID] IN (2,3)
? strwhere2
[mtgType] IN (1,3,11)
? strdescrip
Departments: "RSM Administration", "RSM Activities"
? strdescrip2
Meeting Type: "All Staff", "Safety", "AON Training"

the wherecondition causes error 3071 - "this expression is typed incorrectly,
or is too complex"

given the above, the wherecondition would read as

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[Allen Browne]

You need quotes around the string, i.e.:
wherecondition:= "[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)"

This assumes that DeptID and mtgType are both shown as fields of type Number
when you open your table in design view.

It may help Access to understand the string if you add brackets too. For
debugging purposes, you might like to create 2 strings (strWhere1 and
strWhere2), and then combine them like this:
strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ")"
Debug.Print strWhere

 

[judytjones via AccessMonster.com]

thanks Allen, I have now added the additional quotes and parens to the
strWhere and strWhere2 concat string; which now debug.print as
([DeptID] IN (3)) AND ([mtgType] IN (1,2,11))

still get the 'too complicated' error msg.

I am wondering if the whereCondition is too much on top of my already
complicated report data source, which is
sSQL = "SELECT EmployeeTbl.DeptID, EmployeeTbl.[LastName] & ', ' &
EmployeeTbl.[FirstName] AS EmpName, " & _
"HRqryMeetingForDateSelect.* " & _
"FROM EmployeeTbl LEFT JOIN HRqryMeetingForDateSelect " & _
"ON EmployeeTbl.EmployeeID = HRqryMeetingForDateSelect.EmployeeID;"

the sSQL LEFT JOIN is a fairly recent addition to the previous data source of
HRqryMeetingForDateSelect,
to include current employees in the report that did not attend a mtg in the
selected date range.

first I thought the error was from the report sql code, and did not consider
the calling form's whereCondition.
then I started focusing on the calling form's whereCondition, and ignored
it's impact on the report sql code.
do you think it is the 2 trying to work together that causes the 'too
complicated' error msg?

You need quotes around the string, i.e.:
wherecondition:= "[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)"

This assumes that DeptID and mtgType are both shown as fields of type Number
when you open your table in design view.

It may help Access to understand the string if you add brackets too. For
debugging purposes, you might like to create 2 strings (strWhere1 and
strWhere2), and then combine them like this:
strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ")"
Debug.Print strWhere

I have been trying to adapt the Multi-Select list box code (shown by Allen
Browne) to include 2 list box selections. Allen's code loops through the

[quoted text clipped - 29 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[Allen Browne]

I don't see the mtgType field in your RecordSource.
If it's not there, you can't filter on it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks Allen, I have now added the additional quotes and parens to the
strWhere and strWhere2 concat string; which now debug.print as
([DeptID] IN (3)) AND ([mtgType] IN (1,2,11))

still get the 'too complicated' error msg.

I am wondering if the whereCondition is too much on top of my already
complicated report data source, which is
sSQL = "SELECT EmployeeTbl.DeptID, EmployeeTbl.[LastName] & ', ' &
EmployeeTbl.[FirstName] AS EmpName, " & _
"HRqryMeetingForDateSelect.* " & _
"FROM EmployeeTbl LEFT JOIN HRqryMeetingForDateSelect " & _
"ON EmployeeTbl.EmployeeID = HRqryMeetingForDateSelect.EmployeeID;"

the sSQL LEFT JOIN is a fairly recent addition to the previous data source
of
HRqryMeetingForDateSelect,
to include current employees in the report that did not attend a mtg in
the
selected date range.

first I thought the error was from the report sql code, and did not
consider
the calling form's whereCondition.
then I started focusing on the calling form's whereCondition, and ignored
it's impact on the report sql code.
do you think it is the 2 trying to work together that causes the 'too
complicated' error msg?

You need quotes around the string, i.e.:
wherecondition:= "[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)"

This assumes that DeptID and mtgType are both shown as fields of type
Number
when you open your table in design view.

It may help Access to understand the string if you add brackets too. For
debugging purposes, you might like to create 2 strings (strWhere1 and
strWhere2), and then combine them like this:
strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ")"
Debug.Print strWhere

I have been trying to adapt the Multi-Select list box code (shown by
Allen
Browne) to include 2 list box selections. Allen's code loops through
the

[quoted text clipped - 29 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[judytjones via AccessMonster.com]

thanks for staying with me Allen,

mtgType is in the sSQL in the HRqryMeetingForDateSelect.*
but in researching the field, found out that the mtgTypeID is stored in the
mtg table as text...
so got a chance to use your optional text delimeter, so now debut.print as
([DeptID] IN (3)) AND ([mtgType] IN ("1","2","11"))

but back to the 'too complicated' error msg
decided to pull the 2 field criterias out of the HRqryMeetingForDateSelect.*
and move them to the sSQL LEFT JOIN, so now
sSQL = "SELECT EmployeeTbl.DeptID, EmployeeTbl.[LastName] & ', ' &
EmployeeTbl.[FirstName] AS EmpName, " & _
"HRqryMeetingForDateSelect.* " & _
"FROM EmployeeTbl LEFT JOIN HRqryMeetingForDateSelect " & _
"ON EmployeeTbl.EmployeeID = HRqryMeetingForDateSelect.EmployeeID " & _
"Where ((HRqryMeetingForDateSelect.MtgDate Between #" & _
[Forms]![HRStaffMtgDetailCountSelections]![txtBeginDate] & "# And #" & _
[Forms]![HRStaffMtgDetailCountSelections]![txtEndDate] & _
"#) AND ((EmployeeTbl.TerminationDate) Is Null));"
NO ERROR -yeah! AND the whereCondition is filtering the report to the
appropriate dept(s) and mtgType(s)

BUT... having the criteria here rather than in the query messes up my LEFT
JOIN;
ie. I don't get all current employees, even if they did not attend a meeting
in the date range.

The LEFT JOIN did work when the date range criteria was in the
HRqryMeetingForDateSelect query,
but got 'too complicated' error when run with the whereCondition.

do you know of a way to write this query without 'complicating' the
whereCondition against a query with criteria,
AND still preserving the Employee LEFT JOIN?

fyi- the sql for HRqryMeetingForDateSelect is
SELECT EmployeeTbl.EmployeeID, MtgAttendanceTbl.HrsAttend, AllStaffMtgsTbl.
*
FROM EmployeeTbl INNER JOIN (AllStaffMtgsTbl INNER JOIN MtgAttendanceTbl
ON AllStaffMtgsTbl.MtgDate = MtgAttendanceTbl.MtgDate)

I don't see the mtgType field in your RecordSource.
If it's not there, you can't filter on it.

thanks Allen, I have now added the additional quotes and parens to the
strWhere and strWhere2 concat string; which now debug.print as

[quoted text clipped - 45 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[Allen Browne]

That'll do it: mismatched data types (text field against numeric values)
will is one of the things that will yield "too complex" message. (Basically,
"too complex" is Access-speak for, "I can't figure out how do do what you
asked: it didn't make sense to me.")

There may be a way to include:
OR [YourField] Is Null
in the expression so the outer join still works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for staying with me Allen,

mtgType is in the sSQL in the HRqryMeetingForDateSelect.*
but in researching the field, found out that the mtgTypeID is stored in
the
mtg table as text...
so got a chance to use your optional text delimeter, so now debut.print as
([DeptID] IN (3)) AND ([mtgType] IN ("1","2","11"))

but back to the 'too complicated' error msg
decided to pull the 2 field criterias out of the
HRqryMeetingForDateSelect.*
and move them to the sSQL LEFT JOIN, so now
sSQL = "SELECT EmployeeTbl.DeptID, EmployeeTbl.[LastName] & ', ' &
EmployeeTbl.[FirstName] AS EmpName, " & _
"HRqryMeetingForDateSelect.* " & _
"FROM EmployeeTbl LEFT JOIN HRqryMeetingForDateSelect " & _
"ON EmployeeTbl.EmployeeID = HRqryMeetingForDateSelect.EmployeeID " & _
"Where ((HRqryMeetingForDateSelect.MtgDate Between #" & _
[Forms]![HRStaffMtgDetailCountSelections]![txtBeginDate] & "# And #" &
_
[Forms]![HRStaffMtgDetailCountSelections]![txtEndDate] & _
"#) AND ((EmployeeTbl.TerminationDate) Is Null));"
NO ERROR -yeah! AND the whereCondition is filtering the report to the
appropriate dept(s) and mtgType(s)

BUT... having the criteria here rather than in the query messes up my LEFT
JOIN;
ie. I don't get all current employees, even if they did not attend a
meeting
in the date range.

The LEFT JOIN did work when the date range criteria was in the
HRqryMeetingForDateSelect query,
but got 'too complicated' error when run with the whereCondition.

do you know of a way to write this query without 'complicating' the
whereCondition against a query with criteria,
AND still preserving the Employee LEFT JOIN?

fyi- the sql for HRqryMeetingForDateSelect is
SELECT EmployeeTbl.EmployeeID, MtgAttendanceTbl.HrsAttend,
AllStaffMtgsTbl.
*
FROM EmployeeTbl INNER JOIN (AllStaffMtgsTbl INNER JOIN MtgAttendanceTbl
ON AllStaffMtgsTbl.MtgDate = MtgAttendanceTbl.MtgDate)

I don't see the mtgType field in your RecordSource.
If it's not there, you can't filter on it.

thanks Allen, I have now added the additional quotes and parens to the
strWhere and strWhere2 concat string; which now debug.print as

[quoted text clipped - 45 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[judytjones via AccessMonster.com]

re: mtgDate null as synonymous with "empl did not attend a meeting 'in this
date range'"... proved hard to produce.

I'm sure there are other methods than what I ended up with, but looks simple
enough to follow for the next programmer.

1) moved both meeting criteria (mtgType(s), dateRange back into the 'inner
query' where they belong.
2) to keep from figuring out the proper code for nesting, I used INSERT INTO
meetingCriteriaTempTable.
3) then coded the "Dept LEFT join TempTable" into the report datasource
properties field.
4) the Criteria Selection form uses your listBox loop code;
a) strWhere, to create the Dept Selection filter for the Report Filter
property
b) strDescrip for Dept selection, & strDescrip2 for passing Dept & MtgType
selection strings to the Rpt Header
c) strCriteria (modification of strWhere to remove the field name from the
string), for the inner query DeptType Selection
d) note that MtgType strings made use of the optional text delimeter option

Seems this response took as long to write as the code... but documentation
usually helps me, both to 'clean up code' and 'remember next time'.
Thanks for your time Allen, and for your Access Tips resource.

That'll do it: mismatched data types (text field against numeric values)
will is one of the things that will yield "too complex" message. (Basically,
"too complex" is Access-speak for, "I can't figure out how do do what you
asked: it didn't make sense to me.")

There may be a way to include:
OR [YourField] Is Null
in the expression so the outer join still works.

thanks for staying with me Allen,

[quoted text clipped - 52 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?

 

[judytjones via AccessMonster.com]

re: mtgDate null as synonymous with "empl did not attend a meeting 'in this
date range' "... proved hard to produce.

I'm sure there are other methods than what I ended up with, but looks simple
enough to follow for the next programmer.

1) moved both meeting criteria (mtgType(s), dateRange back into the 'inner
query' where they belong.
2) to keep from figuring out the proper code for nesting, I used INSERT INTO
meetingCriteriaTempTable.
3) then coded the "Dept LEFT join TempTable" into the report datasource
properties field.
4) the Criteria Selection form uses your listBox loop code;
a) strWhere, to create the Dept Selection filter for the Report Filter
property
b) strDescrip & strDescrip2 for passing Dept & MtgType selection strings to
the Rpt Header
c) strCriteria (modification of strWhere to remove the field name from the
string), for the inner query DeptType Selection
d) note that MtgType strings made use of the optional text delimeter option

Seems this response took as long to write as the code... but documentation
usually helps me, both to 'clean up code' and 'remember next time'.
Thanks for your time Allen, and for your Access Tips resource.

That'll do it: mismatched data types (text field against numeric values)
will is one of the things that will yield "too complex" message. (Basically,
[quoted text clipped - 10 lines]

wherecondition:=[DeptID] IN (2,3) AND [mtgType] IN (1,3,11)
is this NOT a valid wherecondition?