Complex query coding Question

I

idtjes3

Hello,

I have a rather complex ( at least I think) SQL code I'm trying to run on
a form which decides the criteria for a report. Basically my form is set up
like this. We have about 10 different items to choose from each with a check
box next to it. If you put a check in the box marked "item" its set to launch
the Item report. Then you place a check in the box for each item you wish to
see on the report. When you made all your selections , hit open and the
report pops up. I had this running fine but i want to be able to add another
option.

I want the user to be able to display the results in which only jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be shown (
same goes for jobs with only panels). The way I had it running was to create
a super long code connecting all the "piece" statements with "Or". I assumed
if i changed Or to "And", it would give me the results i desired. I'm still
able to display all the jobs that contain atleast one of the items i check,
however i cannot get job which contain both criteria only to display. The way
I'm trying to control this is by having a check box called "YesOnly" which
when checked should apply the "Only if the jobs contain all items checked"
code and when unchecked displays all piece checked reguardless if the job
does or doesn't have all. Sorry for the wall of text, I just want to make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) And ([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And ((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or ([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or ((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in advance!
 
A

Allen Browne

So you have a table of jobs - [Job Information], and a related table of the
items in a job - [Job Item List]. In the related table, the Item field
indicates what type of item it is.

a) You have a report that you can filter to show only the specific items
selected in your form.

b) Now you also want to be able to show all details of jobs that contain ANY
one-or-more of the items selected in your form. Is that the question?

If (b) is what you want, it may be easiest to create another report for
this. Create a main report bound to the [Job Information] table, with a
subreport bound to the related table. Then open the main report with a
WhereCondition that uses a subquery to select the job if it has the desired
item in the related table.

The goal will be to build a string like this:
Dim strWhere AS String
strWhere = "ProjectID IN (SELECT [Job Item List].ProjectID FROM [Job
Item List] WHERE [Job Item List].Item IN ('a', 'b', 'c'))"
DoCmd.OpenReport "Report2", acViewPreview, , strWhere

In practice, you will concatenate the items you need into the string, in
place of the examples a, b, and c, based on what the user selected in the
form. This selects all the projects that have any of these items. Then the
subreport shows all the items in those jobs.

On a different note, you say you have (a) already working. Personally, I
would approach it the same way, i.e. leave all the criteria out of the
query, and build a WhereCondition based on the items the user actually
wants. This should be much simpler, and more efficient to execute.

If you want an example of how to build up such a WhereCondition string, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how it works. In the end, the
example applies the results to the filter of a form, but you use the string
in exactly the same way for the WhereCondition of OpenReport.

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

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

idtjes3 said:
I have a rather complex ( at least I think) SQL code I'm trying to run
on
a form which decides the criteria for a report. Basically my form is set
up
like this. We have about 10 different items to choose from each with a
check
box next to it. If you put a check in the box marked "item" its set to
launch
the Item report. Then you place a check in the box for each item you wish
to
see on the report. When you made all your selections , hit open and the
report pops up. I had this running fine but i want to be able to add
another
option.

I want the user to be able to display the results in which only jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be shown
(
same goes for jobs with only panels). The way I had it running was to
create
a super long code connecting all the "piece" statements with "Or". I
assumed
if i changed Or to "And", it would give me the results i desired. I'm
still
able to display all the jobs that contain atleast one of the items i
check,
however i cannot get job which contain both criteria only to display. The
way
I'm trying to control this is by having a check box called "YesOnly" which
when checked should apply the "Only if the jobs contain all items checked"
code and when unchecked displays all piece checked reguardless if the job
does or doesn't have all. Sorry for the wall of text, I just want to make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) And
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or ([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or ((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in
advance!
Click to expand...
 
I

idtjes3

Hi Allen,

To answer your "b" statement, Yes, I already managed to get this part to
work. When I check Items on the form and hit report, all jobs that contain at
least "1" of the items i checked are displayed. The problem I'm running into
is how do I get the report to list only the jobs that contain "All" the items
I check;i.e. If I check columns, panels, and railing, Only the jobs that
contain "All 3" of these items will be shown.

If a job has only columns and panels, it would be filtered out and left
out of the report because it doesn't have railing. Hope that clears things up
a bit. Also, Ill look into that WhereCondition you sent me and see if I can
relate it some how or at least simplify my already working sql equation.

Allen Browne said:
So you have a table of jobs - [Job Information], and a related table of the
items in a job - [Job Item List]. In the related table, the Item field
indicates what type of item it is.

a) You have a report that you can filter to show only the specific items
selected in your form.

b) Now you also want to be able to show all details of jobs that contain ANY
one-or-more of the items selected in your form. Is that the question?

If (b) is what you want, it may be easiest to create another report for
this. Create a main report bound to the [Job Information] table, with a
subreport bound to the related table. Then open the main report with a
WhereCondition that uses a subquery to select the job if it has the desired
item in the related table.

The goal will be to build a string like this:
Dim strWhere AS String
strWhere = "ProjectID IN (SELECT [Job Item List].ProjectID FROM [Job
Item List] WHERE [Job Item List].Item IN ('a', 'b', 'c'))"
DoCmd.OpenReport "Report2", acViewPreview, , strWhere

In practice, you will concatenate the items you need into the string, in
place of the examples a, b, and c, based on what the user selected in the
form. This selects all the projects that have any of these items. Then the
subreport shows all the items in those jobs.

On a different note, you say you have (a) already working. Personally, I
would approach it the same way, i.e. leave all the criteria out of the
query, and build a WhereCondition based on the items the user actually
wants. This should be much simpler, and more efficient to execute.

If you want an example of how to build up such a WhereCondition string, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how it works. In the end, the
example applies the results to the filter of a form, but you use the string
in exactly the same way for the WhereCondition of OpenReport.

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

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

idtjes3 said:
I have a rather complex ( at least I think) SQL code I'm trying to run
on
a form which decides the criteria for a report. Basically my form is set
up
like this. We have about 10 different items to choose from each with a
check
box next to it. If you put a check in the box marked "item" its set to
launch
the Item report. Then you place a check in the box for each item you wish
to
see on the report. When you made all your selections , hit open and the
report pops up. I had this running fine but i want to be able to add
another
option.

I want the user to be able to display the results in which only jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be shown
(
same goes for jobs with only panels). The way I had it running was to
create
a super long code connecting all the "piece" statements with "Or". I
assumed
if i changed Or to "And", it would give me the results i desired. I'm
still
able to display all the jobs that contain atleast one of the items i
check,
however i cannot get job which contain both criteria only to display. The
way
I'm trying to control this is by having a check box called "YesOnly" which
when checked should apply the "Only if the jobs contain all items checked"
code and when unchecked displays all piece checked reguardless if the job
does or doesn't have all. Sorry for the wall of text, I just want to make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) And
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or ([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or ((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project Inquiry]!txtSpecialPcs,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in
advance!
Click to expand...
Click to expand...
 
A

Allen Browne

If the [Job Item List] allows only ONE of each type per project, you could
build this kind of WhereCondition string:

strWhere =
"(SELECT Count([Job Item List].ProjectID) AS HowMany
FROM [Job Item List]
WHERE [Job Item List].ProjectID = tblProject.ProjectID
AND [Job Item List].Item IN ('a', 'b', 'c')) = 3"

If it allows multiples, build a query that does a:
SELECT DISTINCT ProjectID, Item
FROM [Job Item List];
and then use that in the subquery.

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

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

idtjes3 said:
Hi Allen,

To answer your "b" statement, Yes, I already managed to get this part to
work. When I check Items on the form and hit report, all jobs that contain
at
least "1" of the items i checked are displayed. The problem I'm running
into
is how do I get the report to list only the jobs that contain "All" the
items
I check;i.e. If I check columns, panels, and railing, Only the jobs that
contain "All 3" of these items will be shown.

If a job has only columns and panels, it would be filtered out and left
out of the report because it doesn't have railing. Hope that clears things
up
a bit. Also, Ill look into that WhereCondition you sent me and see if I
can
relate it some how or at least simplify my already working sql equation.

Allen Browne said:
So you have a table of jobs - [Job Information], and a related table of
the
items in a job - [Job Item List]. In the related table, the Item field
indicates what type of item it is.

a) You have a report that you can filter to show only the specific items
selected in your form.

b) Now you also want to be able to show all details of jobs that contain
ANY
one-or-more of the items selected in your form. Is that the question?

If (b) is what you want, it may be easiest to create another report for
this. Create a main report bound to the [Job Information] table, with a
subreport bound to the related table. Then open the main report with a
WhereCondition that uses a subquery to select the job if it has the
desired
item in the related table.

The goal will be to build a string like this:
Dim strWhere AS String
strWhere = "ProjectID IN (SELECT [Job Item List].ProjectID FROM [Job
Item List] WHERE [Job Item List].Item IN ('a', 'b', 'c'))"
DoCmd.OpenReport "Report2", acViewPreview, , strWhere

In practice, you will concatenate the items you need into the string, in
place of the examples a, b, and c, based on what the user selected in the
form. This selects all the projects that have any of these items. Then
the
subreport shows all the items in those jobs.

On a different note, you say you have (a) already working. Personally, I
would approach it the same way, i.e. leave all the criteria out of the
query, and build a WhereCondition based on the items the user actually
wants. This should be much simpler, and more efficient to execute.

If you want an example of how to build up such a WhereCondition string,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how it works. In the end,
the
example applies the results to the filter of a form, but you use the
string
in exactly the same way for the WhereCondition of OpenReport.

idtjes3 said:
I have a rather complex ( at least I think) SQL code I'm trying to
run
on
a form which decides the criteria for a report. Basically my form is
set
up
like this. We have about 10 different items to choose from each with a
check
box next to it. If you put a check in the box marked "item" its set to
launch
the Item report. Then you place a check in the box for each item you
wish
to
see on the report. When you made all your selections , hit open and the
report pops up. I had this running fine but i want to be able to add
another
option.

I want the user to be able to display the results in which only jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be
shown
(
same goes for jobs with only panels). The way I had it running was to
create
a super long code connecting all the "piece" statements with "Or". I
assumed
if i changed Or to "And", it would give me the results i desired. I'm
still
able to display all the jobs that contain atleast one of the items i
check,
however i cannot get job which contain both criteria only to display.
The
way
I'm trying to control this is by having a check box called "YesOnly"
which
when checked should apply the "Only if the jobs contain all items
checked"
code and when unchecked displays all piece checked reguardless if the
job
does or doesn't have all. Sorry for the wall of text, I just want to
make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item
List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) And
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
I

idtjes3

Yes, there could be say multiple styles of one item within a project. How
exactly would I incorporate the use of my check boxes into the second
equation? I would need to somehow have the equation check the "YesOnly" check
box on the form to see if its checked, then filter the reports based on
whatever items I check. Thanks for your help so far. I'm still pretty new to
this program and I'm kind of teaching myself as I go along.

Allen Browne said:
If the [Job Item List] allows only ONE of each type per project, you could
build this kind of WhereCondition string:

strWhere =
"(SELECT Count([Job Item List].ProjectID) AS HowMany
FROM [Job Item List]
WHERE [Job Item List].ProjectID = tblProject.ProjectID
AND [Job Item List].Item IN ('a', 'b', 'c')) = 3"

If it allows multiples, build a query that does a:
SELECT DISTINCT ProjectID, Item
FROM [Job Item List];
and then use that in the subquery.

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

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

idtjes3 said:
Hi Allen,

To answer your "b" statement, Yes, I already managed to get this part to
work. When I check Items on the form and hit report, all jobs that contain
at
least "1" of the items i checked are displayed. The problem I'm running
into
is how do I get the report to list only the jobs that contain "All" the
items
I check;i.e. If I check columns, panels, and railing, Only the jobs that
contain "All 3" of these items will be shown.

If a job has only columns and panels, it would be filtered out and left
out of the report because it doesn't have railing. Hope that clears things
up
a bit. Also, Ill look into that WhereCondition you sent me and see if I
can
relate it some how or at least simplify my already working sql equation.

Allen Browne said:
So you have a table of jobs - [Job Information], and a related table of
the
items in a job - [Job Item List]. In the related table, the Item field
indicates what type of item it is.

a) You have a report that you can filter to show only the specific items
selected in your form.

b) Now you also want to be able to show all details of jobs that contain
ANY
one-or-more of the items selected in your form. Is that the question?

If (b) is what you want, it may be easiest to create another report for
this. Create a main report bound to the [Job Information] table, with a
subreport bound to the related table. Then open the main report with a
WhereCondition that uses a subquery to select the job if it has the
desired
item in the related table.

The goal will be to build a string like this:
Dim strWhere AS String
strWhere = "ProjectID IN (SELECT [Job Item List].ProjectID FROM [Job
Item List] WHERE [Job Item List].Item IN ('a', 'b', 'c'))"
DoCmd.OpenReport "Report2", acViewPreview, , strWhere

In practice, you will concatenate the items you need into the string, in
place of the examples a, b, and c, based on what the user selected in the
form. This selects all the projects that have any of these items. Then
the
subreport shows all the items in those jobs.

On a different note, you say you have (a) already working. Personally, I
would approach it the same way, i.e. leave all the criteria out of the
query, and build a WhereCondition based on the items the user actually
wants. This should be much simpler, and more efficient to execute.

If you want an example of how to build up such a WhereCondition string,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how it works. In the end,
the
example applies the results to the filter of a form, but you use the
string
in exactly the same way for the WhereCondition of OpenReport.


I have a rather complex ( at least I think) SQL code I'm trying to
run
on
a form which decides the criteria for a report. Basically my form is
set
up
like this. We have about 10 different items to choose from each with a
check
box next to it. If you put a check in the box marked "item" its set to
launch
the Item report. Then you place a check in the box for each item you
wish
to
see on the report. When you made all your selections , hit open and the
report pops up. I had this running fine but i want to be able to add
another
option.

I want the user to be able to display the results in which only jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be
shown
(
same goes for jobs with only panels). The way I had it running was to
create
a super long code connecting all the "piece" statements with "Or". I
assumed
if i changed Or to "And", it would give me the results i desired. I'm
still
able to display all the jobs that contain atleast one of the items i
check,
however i cannot get job which contain both criteria only to display.
The
way
I'm trying to control this is by having a check box called "YesOnly"
which
when checked should apply the "Only if the jobs contain all items
checked"
code and when unchecked displays all piece checked reguardless if the
job
does or doesn't have all. Sorry for the wall of text, I just want to
make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item
List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) And
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project Inquiry]!txtSpindles,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project Inquiry]!txtBrackets,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project Inquiry]!txtSpandrels,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null))) Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Say you save this query as qryProjectItem:
SELECT DISTINCT ProjectID, Item
FROM [Job Item List];

You now OpenReport with something like this in the WhereCondition string:
strWhere = "ProjectID IN (SELECT ProjectID FROM qryProjectItem " & _
"WHERE qryProjectItem.Item IN ('Columns', 'Panels', 'Railing') " & _
"GROUP BY ProjectID HAVING Count(Item) = 3)"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Obviously you have to concatenate the relvant values into the string
depending which boxes are checked, and change the 3 to however many were
checked.

The core concept is that qryProjectItem returns one record for each type of
item in each project. The subquery then matches only the projects that have
all 3 (or all however-many) found in that query.

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

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

idtjes3 said:
Yes, there could be say multiple styles of one item within a project. How
exactly would I incorporate the use of my check boxes into the second
equation? I would need to somehow have the equation check the "YesOnly"
check
box on the form to see if its checked, then filter the reports based on
whatever items I check. Thanks for your help so far. I'm still pretty new
to
this program and I'm kind of teaching myself as I go along.

Allen Browne said:
If the [Job Item List] allows only ONE of each type per project, you
could
build this kind of WhereCondition string:

strWhere =
"(SELECT Count([Job Item List].ProjectID) AS HowMany
FROM [Job Item List]
WHERE [Job Item List].ProjectID = tblProject.ProjectID
AND [Job Item List].Item IN ('a', 'b', 'c')) = 3"

If it allows multiples, build a query that does a:
SELECT DISTINCT ProjectID, Item
FROM [Job Item List];
and then use that in the subquery.

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

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

idtjes3 said:
Hi Allen,

To answer your "b" statement, Yes, I already managed to get this part
to
work. When I check Items on the form and hit report, all jobs that
contain
at
least "1" of the items i checked are displayed. The problem I'm running
into
is how do I get the report to list only the jobs that contain "All" the
items
I check;i.e. If I check columns, panels, and railing, Only the jobs
that
contain "All 3" of these items will be shown.

If a job has only columns and panels, it would be filtered out and
left
out of the report because it doesn't have railing. Hope that clears
things
up
a bit. Also, Ill look into that WhereCondition you sent me and see if I
can
relate it some how or at least simplify my already working sql
equation.

:

So you have a table of jobs - [Job Information], and a related table
of
the
items in a job - [Job Item List]. In the related table, the Item field
indicates what type of item it is.

a) You have a report that you can filter to show only the specific
items
selected in your form.

b) Now you also want to be able to show all details of jobs that
contain
ANY
one-or-more of the items selected in your form. Is that the question?

If (b) is what you want, it may be easiest to create another report
for
this. Create a main report bound to the [Job Information] table, with
a
subreport bound to the related table. Then open the main report with a
WhereCondition that uses a subquery to select the job if it has the
desired
item in the related table.

The goal will be to build a string like this:
Dim strWhere AS String
strWhere = "ProjectID IN (SELECT [Job Item List].ProjectID FROM
[Job
Item List] WHERE [Job Item List].Item IN ('a', 'b', 'c'))"
DoCmd.OpenReport "Report2", acViewPreview, , strWhere

In practice, you will concatenate the items you need into the string,
in
place of the examples a, b, and c, based on what the user selected in
the
form. This selects all the projects that have any of these items. Then
the
subreport shows all the items in those jobs.

On a different note, you say you have (a) already working. Personally,
I
would approach it the same way, i.e. leave all the criteria out of the
query, and build a WhereCondition based on the items the user actually
wants. This should be much simpler, and more efficient to execute.

If you want an example of how to build up such a WhereCondition
string,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how it works. In the
end,
the
example applies the results to the filter of a form, but you use the
string
in exactly the same way for the WhereCondition of OpenReport.


I have a rather complex ( at least I think) SQL code I'm trying to
run
on
a form which decides the criteria for a report. Basically my form is
set
up
like this. We have about 10 different items to choose from each with
a
check
box next to it. If you put a check in the box marked "item" its set
to
launch
the Item report. Then you place a check in the box for each item you
wish
to
see on the report. When you made all your selections , hit open and
the
report pops up. I had this running fine but i want to be able to add
another
option.

I want the user to be able to display the results in which only
jobs
containing "ALL" the pieces checked will be displayed. ie if I check
"columns" and "panels" checked, jobs only having columns will not be
shown
(
same goes for jobs with only panels). The way I had it running was
to
create
a super long code connecting all the "piece" statements with "Or". I
assumed
if i changed Or to "And", it would give me the results i desired.
I'm
still
able to display all the jobs that contain atleast one of the items i
check,
however i cannot get job which contain both criteria only to
display.
The
way
I'm trying to control this is by having a check box called "YesOnly"
which
when checked should apply the "Only if the jobs contain all items
checked"
code and when unchecked displays all piece checked reguardless if
the
job
does or doesn't have all. Sorry for the wall of text, I just want to
make
sure im explaining this correctly. Heres the code I'm using:

SELECT [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job
Item
List].Molds, [Job Item List].Plugs
FROM [Job Information] LEFT JOIN [Job Item List] ON [Job
Information].ProjectID=[Job Item List].ProjectID
GROUP BY [Job Information].ProjectID, [Job
Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].SqFt, [Job Item List].Units, [Job
Item
List].Molds, [Job Item List].Plugs
HAVING IIF(Forms![Project Inquiry]!YesOnly=True,((([Job Item
List].Item)
Like IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) And ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) And ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null)
And
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
And ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project
Inquiry]!txtSpindles,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) And
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project
Inquiry]!txtBrackets,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project
Inquiry]!txtSpandrels,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
And
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))),((([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesTerracotta=True,Forms![Project
Inquiry]!txtTerracotta,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project Inquiry]!YesPanels=True,Forms![Project
Inquiry]!txtPanels,Null) Or ([Job Item List].Item) Like
IIf(Forms![Project
Inquiry]!YesCornice=True,Forms![Project Inquiry]!txtCornice,Null) Or
([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesColumnCovers=True,Forms![Project
Inquiry]!txtColumnCovers,Null)))
Or ((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesRailing=True,Forms![Project Inquiry]!txtRailing,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpindles=True,Forms![Project
Inquiry]!txtSpindles,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesPosts=True,Forms![Project Inquiry]!txtPosts,Null))) Or
((([Job
Item List].Item) Like IIf(Forms![Project
Inquiry]!YesBrackets=True,Forms![Project
Inquiry]!txtBrackets,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpandrels=True,Forms![Project
Inquiry]!txtSpandrels,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesParapet=True,Forms![Project Inquiry]!txtParapet,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialPcs=True,Forms![Project
Inquiry]!txtSpecialPcs,Null)))
Or
((([Job Item List].Item) Like IIf(Forms![Project
Inquiry]!YesSpecialLinFt=True,Forms![Project
Inquiry]!txtSpecialLinFt,Null))));

also if theres a simpler approach to this, I'm all ears. Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
I

idtjes3

So every time I want to add or take away items in the search do I have to
change the "3" to however many criteria I'm searching for? In the same sense,
wouldn't I have to add to the list of items in the "IN" part of the
statement? For example, Would it have to become; "WHERE qryProjectItem.Item
IN ('Columns', 'Panels', 'Railing', 'Spindles') " & _ ..... if I I wanted to
search for a job that contained these 4 items? If I'm reading this right It
seems I would have to constantly alter the code to suit what ever situation I
needed it for.
 
A

Allen Browne

No. The 3 is not in a saved query.

The 3 is in the filter string that you build programmatically when you
OpenReport.

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

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

idtjes3 said:
So every time I want to add or take away items in the search do I have
to
change the "3" to however many criteria I'm searching for? In the same
sense,
wouldn't I have to add to the list of items in the "IN" part of the
statement? For example, Would it have to become; "WHERE
qryProjectItem.Item
IN ('Columns', 'Panels', 'Railing', 'Spindles') " & _ ..... if I I wanted
to
search for a job that contained these 4 items? If I'm reading this right
It
seems I would have to constantly alter the code to suit what ever
situation I
needed it for.

Allen Browne said:
Say you save this query as qryProjectItem:
SELECT DISTINCT ProjectID, Item
FROM [Job Item List];

You now OpenReport with something like this in the WhereCondition string:
strWhere = "ProjectID IN (SELECT ProjectID FROM qryProjectItem " & _
"WHERE qryProjectItem.Item IN ('Columns', 'Panels', 'Railing') "
& _
"GROUP BY ProjectID HAVING Count(Item) = 3)"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Obviously you have to concatenate the relvant values into the string
depending which boxes are checked, and change the 3 to however many were
checked.

The core concept is that qryProjectItem returns one record for each type
of
item in each project. The subquery then matches only the projects that
have
all 3 (or all however-many) found in that query.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Yes/ No box being ingnored in query 10
Filter Query by Year on a form 13
FWT Newsletter - Weekly - November 17, 2003 0
WTD: Nvidia Videocard for 4x AGP Slot 0

Top