Yes/ No box being ingnored in query

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;
 
D

Douglas J. Steele

You cannot use an IIf statement to change a condition to Like.

Try setting the condition to the following:

Like [Forms]![Project Inquiry]![txtColumns] And Nz([Forms]![Project
Inquiry]![YesColumns], True)

That assumes you don't want anything returned if the YesColumns check box is
unchecked. If you want everything returned if it's not checked, and only
what matches the contents of txtColumns if it is checked, try

Like [Forms]![Project Inquiry]![txtColumns] Or Nz([Forms]![Project
Inquiry]![YesColumns], False) = False

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


idtjes3 said:
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;
Click to expand...
 
I

idtjes3

Thank you Doug for your quick response.

That condition didn't work at first but with some tweaking it eventually
gave me the results I needed. I changed the code to: Like [Forms]![Project
Inquiry]![txtColumns] And ([Forms]![Project Inquiry]![YesColumns]=True).

Now for a follow up if you don't mind. How exactly would I add to this
condition to allow for more items to be chosen such as say Jobs with stairs
or jobs with patios etc.

Douglas J. Steele said:
You cannot use an IIf statement to change a condition to Like.

Try setting the condition to the following:

Like [Forms]![Project Inquiry]![txtColumns] And Nz([Forms]![Project
Inquiry]![YesColumns], True)

That assumes you don't want anything returned if the YesColumns check box is
unchecked. If you want everything returned if it's not checked, and only
what matches the contents of txtColumns if it is checked, try

Like [Forms]![Project Inquiry]![txtColumns] Or Nz([Forms]![Project
Inquiry]![YesColumns], False) = False

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


idtjes3 said:
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;
Click to expand...
Click to expand...
 
D

Douglas J. Steele

It just occurred to me that it's easier to set the condition to

Like IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
You cannot use an IIf statement to change a condition to Like.

Try setting the condition to the following:

Like [Forms]![Project Inquiry]![txtColumns] And Nz([Forms]![Project
Inquiry]![YesColumns], True)

That assumes you don't want anything returned if the YesColumns check box
is unchecked. If you want everything returned if it's not checked, and
only what matches the contents of txtColumns if it is checked, try

Like [Forms]![Project Inquiry]![txtColumns] Or Nz([Forms]![Project
Inquiry]![YesColumns], False) = False

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


idtjes3 said:
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;
Click to expand...
Click to expand...
 
I

idtjes3

Ah so using "Like IIF" i could then chain as many items as i needed then? Is
there a reason why access woun't let you use " IIF" in the criteria, but "
Like IIF" is ok?

Douglas J. Steele said:
It just occurred to me that it's easier to set the condition to

Like IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
You cannot use an IIf statement to change a condition to Like.

Try setting the condition to the following:

Like [Forms]![Project Inquiry]![txtColumns] And Nz([Forms]![Project
Inquiry]![YesColumns], True)

That assumes you don't want anything returned if the YesColumns check box
is unchecked. If you want everything returned if it's not checked, and
only what matches the contents of txtColumns if it is checked, try

Like [Forms]![Project Inquiry]![txtColumns] Or Nz([Forms]![Project
Inquiry]![YesColumns], False) = False

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


idtjes3 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The problem wasn't that you were using IIf as a criteria: the problem was
that you had Like inside your IIf statement.

The comparison operator cannot be inside the IIf statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


idtjes3 said:
Ah so using "Like IIF" i could then chain as many items as i needed then?
Is
there a reason why access woun't let you use " IIF" in the criteria, but "
Like IIF" is ok?

Douglas J. Steele said:
It just occurred to me that it's easier to set the condition to

Like IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
You cannot use an IIf statement to change a condition to Like.

Try setting the condition to the following:

Like [Forms]![Project Inquiry]![txtColumns] And Nz([Forms]![Project
Inquiry]![YesColumns], True)

That assumes you don't want anything returned if the YesColumns check
box
is unchecked. If you want everything returned if it's not checked, and
only what matches the contents of txtColumns if it is checked, try

Like [Forms]![Project Inquiry]![txtColumns] Or Nz([Forms]![Project
Inquiry]![YesColumns], False) = False

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




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;
Click to expand...
Click to expand...
Click to expand...
 
P

Pat Hartman

The purpose of the Like operator is to perform fuzzy matches. If you are
not using a wild card in the match string, you should NOT be using like.
You should be using =.

Under some circumstances, the use of Like will prevent Jet from using an
index to find data. It will use a full table scan instead. Use = unless
you actually need a fuzzy match.
 
D

Douglas J. Steele

Since it's a text box on the form, I assumed the user was providing the wild
card character(s).

Otherwise, I agree 100% with you, Pat.
 
I

idtjes3

It is a text box however the data is already in there and the user has no
control over editing the contents. I set it up this way cause its the only
way i could think of to accurately search the values in the table without
worrying about spelling mistakes and such. Ani ideas on how to link multiple
items ? Ive been trying for the last half hour but no luck heres what i've
tried: Like IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], IIf([Forms]![Project Inquiry]![YesCornice]=True,
[Forms]![Project Inquiry]![txtCornice], Null) . this will let me choose one
or the other but not both ( as expected) Ive also tried sperating IIF's with
AND but this returns no results. Thanks for all your help so far.
 
D

Douglas J. Steele

I don't understand what you mean by linking multiple items.

Are you saying that you want to seach a single field for what's in
[Forms]![Project Inquiry]![txtColumns] or what's in [Forms]![Project
Inquiry]![txtCornice]?

If so, try:

IN (IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], IIf([Forms]![Project Inquiry]![YesCornice]=True,
[Forms]![Project Inquiry]![txtCornice], Null))

Note that Pat's absolutely correct that there's no point in using Like
unless there are wild card characters in the text. (And even if there are
wildcards, Like won't catch spelling mistakes!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


idtjes3 said:
It is a text box however the data is already in there and the user has no
control over editing the contents. I set it up this way cause its the only
way i could think of to accurately search the values in the table without
worrying about spelling mistakes and such. Ani ideas on how to link
multiple
items ? Ive been trying for the last half hour but no luck heres what i've
tried: Like IIf([Forms]![Project Inquiry]![YesColumns]=True,
[Forms]![Project
Inquiry]![txtColumns], IIf([Forms]![Project Inquiry]![YesCornice]=True,
[Forms]![Project Inquiry]![txtCornice], Null) . this will let me choose
one
or the other but not both ( as expected) Ive also tried sperating IIF's
with
AND but this returns no results. Thanks for all your help so far.


Douglas J. Steele said:
Since it's a text box on the form, I assumed the user was providing the
wild
card character(s).

Otherwise, I agree 100% with you, Pat.
Click to expand...
Click to expand...
 
I

idtjes3

Hello,

Thanks for all your help guys I was able to fix the problem on friday.
Heres what I did: In the query builder under the "Item" criteria i entered
the first IIF,
Like IIf([Forms]![Project Inquiry]![YesColumns]=True,[Forms]![Project
Inquiry]![txtColumns],Null). then where it says or, i kept adding the
equation for every item i wanted to be able to search for ( changing out the
"Column" for what ever it was I wanted to search for.) It works now and i am
able to search for just one item or multiple items. Its strange though, i
would of thought adding equations to the " or" boxes would of given me
different results. Now just to clear things up, I should be able to swap the
"Like" in the equations for "In" to make it a better expression?

Douglas J. Steele said:
I don't understand what you mean by linking multiple items.

Are you saying that you want to seach a single field for what's in
[Forms]![Project Inquiry]![txtColumns] or what's in [Forms]![Project
Inquiry]![txtCornice]?

If so, try:

IN (IIf([Forms]![Project Inquiry]![YesColumns]=True, [Forms]![Project
Inquiry]![txtColumns], IIf([Forms]![Project Inquiry]![YesCornice]=True,
[Forms]![Project Inquiry]![txtCornice], Null))

Note that Pat's absolutely correct that there's no point in using Like
unless there are wild card characters in the text. (And even if there are
wildcards, Like won't catch spelling mistakes!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


idtjes3 said:
It is a text box however the data is already in there and the user has no
control over editing the contents. I set it up this way cause its the only
way i could think of to accurately search the values in the table without
worrying about spelling mistakes and such. Ani ideas on how to link
multiple
items ? Ive been trying for the last half hour but no luck heres what i've
tried: Like IIf([Forms]![Project Inquiry]![YesColumns]=True,
[Forms]![Project
Inquiry]![txtColumns], IIf([Forms]![Project Inquiry]![YesCornice]=True,
[Forms]![Project Inquiry]![txtCornice], Null) . this will let me choose
one
or the other but not both ( as expected) Ive also tried sperating IIF's
with
AND but this returns no results. Thanks for all your help so far.


Douglas J. Steele said:
Since it's a text box on the form, I assumed the user was providing the
wild
card character(s).

Otherwise, I agree 100% with you, Pat.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The purpose of the Like operator is to perform fuzzy matches. If you
are
not using a wild card in the match string, you should NOT be using
like.
You should be using =.

Under some circumstances, the use of Like will prevent Jet from using
an
index to find data. It will use a full table scan instead. Use =
unless
you actually need a fuzzy match.
Click to expand...
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

Complex query coding Question 7
Help with QBF CheckBox Query 2
how to cause checked yes/no items to appear in a list in another part of the form... 8
Trouble changing the Row Source for a Combo Box Access 2007 - SQL 2000 1
Combo box in Forms 1
Check box to control editablility of text box and return all recor 1
list box focus? question 7
Query Criteria Not Working 2

Top