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!
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!