I
idtjes3
I have created a form that displays all the products my company makes (
yes i know i might run into a problem " What if my company ads products?",but
i wont. We are pretty set in our ways.). Next to each product is a check box.
The purpose of the box is to display all job we have done in the past that
contain that product ( by checking it yes) I have created a query that
displays all our items from every job. The way i have it set is the query
looks at a text box on the form, lets say "txtColumns" which has a value of
="Columns". Now in the criteria for items i have: Like [Forms]![Project
Inquiry]![txtColumns]. This works and displays all Column items from every
job.
The problem occurs when i try to control the "columns" with a yes or no
box. I wrote the code: IIf([Forms]![Project Inquiry]![YesColumns]=True, Like
[Forms]![Project Inquiry]![txtColumns],Null), where "YesColumns" is the name
of the check box. When i close and save the query then reopen it in design
mode, the code is changed to: IIf([Forms]![Project
Inquiry]![YesColumns]="True",([Job Item List].[Item]) Like [Forms]![Project
Inquiry]![txtColumns],Null). This new criteria displays no information in
datasheet view. Do I need to specify a "Group By" and switch this to an
equation? Could someone explain why this isnt working? It seems like it
should.
Also, heres the sql code. it might not make much sense but ill list it just
the same:
SELECT [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].LinFt, [Job Item List].Units, [Job Item
List].Molds
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].LinFt, [Job Item List].Units, [Job Item
List].Molds
HAVING ((([Job Item List].Item)=IIf([Forms]![Project
Inquiry]![YesColumns]="True",([Job Item List].[Item]) Like [Forms]![Project
Inquiry]![txtColumns],Null)))
ORDER BY [Job Information].ProjectID DESC;
yes i know i might run into a problem " What if my company ads products?",but
i wont. We are pretty set in our ways.). Next to each product is a check box.
The purpose of the box is to display all job we have done in the past that
contain that product ( by checking it yes) I have created a query that
displays all our items from every job. The way i have it set is the query
looks at a text box on the form, lets say "txtColumns" which has a value of
="Columns". Now in the criteria for items i have: Like [Forms]![Project
Inquiry]![txtColumns]. This works and displays all Column items from every
job.
The problem occurs when i try to control the "columns" with a yes or no
box. I wrote the code: IIf([Forms]![Project Inquiry]![YesColumns]=True, Like
[Forms]![Project Inquiry]![txtColumns],Null), where "YesColumns" is the name
of the check box. When i close and save the query then reopen it in design
mode, the code is changed to: IIf([Forms]![Project
Inquiry]![YesColumns]="True",([Job Item List].[Item]) Like [Forms]![Project
Inquiry]![txtColumns],Null). This new criteria displays no information in
datasheet view. Do I need to specify a "Group By" and switch this to an
equation? Could someone explain why this isnt working? It seems like it
should.
Also, heres the sql code. it might not make much sense but ill list it just
the same:
SELECT [Job Information].ProjectID, [Job Information].ProjectDescription,
[Job Item List].Item, [Job Item List].ItemDescription, [Job Item
List].[Pc(s)], [Job Item List].LinFt, [Job Item List].Units, [Job Item
List].Molds
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].LinFt, [Job Item List].Units, [Job Item
List].Molds
HAVING ((([Job Item List].Item)=IIf([Forms]![Project
Inquiry]![YesColumns]="True",([Job Item List].[Item]) Like [Forms]![Project
Inquiry]![txtColumns],Null)))
ORDER BY [Job Information].ProjectID DESC;