Confused query

[Zippy]

I've got a query which asks the question Enter Project A or B. This part of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want a
modified Project A included Y/N?]. The modified Project A is indicated in
the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the Yes or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong in
asking a double questioned query?

Zippy

 

[Arvin Meyer [MVP]]

Try using -1 for yes and 0 for no.

 

[J_Goddard via AccessMonster.com]

Hi -

Use an expression like

IIf([Do you want a modified Project A included Y/N?]="Y",-1,0)

in the criteria.

John

I've got a query which asks the question Enter Project A or B. This part of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want a
modified Project A included Y/N?]. The modified Project A is indicated in
the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the Yes or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong in
asking a double questioned query?

Zippy

 

[Zippy]

Thanks for that - it's not giving me an error now but I'm not getting the
answer I'm expecting (which clearly means I'm asking the wrong question!).

In the query under the Project type I have [Enter Project A or Project B]
then on the same line but under the modded field I ask [Include modded
results? 1 for yes 0 for No]

But what I really want it to do is to give me all Project A results (modded
or not) if I select Project A and if I select Project B I want the modded
Project A's to appear if I say Yes to the include modded results question.
So have I set it up wrong or is it too complicated for a select query?

Zippy

Try using -1 for yes and 0 for no.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I've got a query which asks the question Enter Project A or B. This part
of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want
a
modified Project A included Y/N?]. The modified Project A is indicated in
the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the Yes
or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong in
asking a double questioned query?

Zippy

 

[Arvin Meyer [MVP]]

I think that you might just need to use:
-1
instead of:
1

In a Boolean (yes/no) field in Access -1 is True, and 0 is False. Usually,
anything that is non-zero will be read as True, but don't count on it.

You need to use code to get an answer because you can branch the SQL with an
If ... Then statement.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for that - it's not giving me an error now but I'm not getting the
answer I'm expecting (which clearly means I'm asking the wrong question!).

In the query under the Project type I have [Enter Project A or Project B]
then on the same line but under the modded field I ask [Include modded
results? 1 for yes 0 for No]

But what I really want it to do is to give me all Project A results
(modded
or not) if I select Project A and if I select Project B I want the modded
Project A's to appear if I say Yes to the include modded results question.
So have I set it up wrong or is it too complicated for a select query?

Zippy

Try using -1 for yes and 0 for no.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I've got a query which asks the question Enter Project A or B. This part
of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want
a
modified Project A included Y/N?]. The modified Project A is indicated in
the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the
Yes
or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong
in
asking a double questioned query?

Zippy

 

[Zippy]

OK thanks for that I'll have another try tomorrow.

Zippy

I think that you might just need to use:
-1
instead of:
1

In a Boolean (yes/no) field in Access -1 is True, and 0 is False. Usually,
anything that is non-zero will be read as True, but don't count on it.

You need to use code to get an answer because you can branch the SQL with an
If ... Then statement.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for that - it's not giving me an error now but I'm not getting the
answer I'm expecting (which clearly means I'm asking the wrong question!).

In the query under the Project type I have [Enter Project A or Project B]
then on the same line but under the modded field I ask [Include modded
results? 1 for yes 0 for No]

But what I really want it to do is to give me all Project A results
(modded
or not) if I select Project A and if I select Project B I want the modded
Project A's to appear if I say Yes to the include modded results question.
So have I set it up wrong or is it too complicated for a select query?

Zippy

Try using -1 for yes and 0 for no.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I've got a query which asks the question Enter Project A or B. This part
of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want
a
modified Project A included Y/N?]. The modified Project A is

indicated
in

the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the
Yes
or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong
in
asking a double questioned query?

Zippy

 

[Zippy]

Thanks John,

That almost gives me the answer I want - but if I ask for Project B without
modified Project A, I get the unmodified Project A's included in the answer
which I don't want.

Zippy

Hi -

Use an expression like

IIf([Do you want a modified Project A included Y/N?]="Y",-1,0)

in the criteria.

John

I've got a query which asks the question Enter Project A or B. This part of
the query works fine on its own. I also want to ask in the same query
whether a "modified" Project A should also be included as in [Do you want a
modified Project A included Y/N?]. The modified Project A is indicated in
the table by a Yes/No field. If I type a Yes or No directly into the
query it works the way I expect but if I use the question to get the Yes or
No both questions are asked correctly but the result fails with a "too
complicated expression" error

I'm sure it can be done so could anyone tell me where I'm going wrong in
asking a double questioned query?

Zippy

 

[J_Goddard via AccessMonster.com]

Hi -

Try this version:

IIf([Do you want a modified Project A included Y/N?]="Y",-1,2)

I just tried it and it worked fine, since a Yes/No is never "2".

John

Thanks John,

That almost gives me the answer I want - but if I ask for Project B without
modified Project A, I get the unmodified Project A's included in the answer
which I don't want.

Zippy

Hi -

[quoted text clipped - 19 lines]

 

[Zippy]

That works fine if I choose Project B. Is there a way to make it the same
question work with the Project A's ie Can I also choose to have "plain"
Project A and/or Modified Project A or would that need another query.

Thanks very much for help so far by the way. It's much appreciated.

Zippy

Hi -

Try this version:

IIf([Do you want a modified Project A included Y/N?]="Y",-1,2)

I just tried it and it worked fine, since a Yes/No is never "2".

John

Thanks John,

That almost gives me the answer I want - but if I ask for Project B without
modified Project A, I get the unmodified Project A's included in the answer
which I don't want.

Zippy

Hi -

[quoted text clipped - 19 lines]

Zippy

 

[J_Goddard via AccessMonster.com]

Hi -

Can you clarify the logic a bit?

If you select project "B", you ask if Modified project "A" is to be included
as well.

But if you select Project "A", are there three options - Modified Only, Un-
Modified only or both?

John

That works fine if I choose Project B. Is there a way to make it the same
question work with the Project A's ie Can I also choose to have "plain"
Project A and/or Modified Project A or would that need another query.

Thanks very much for help so far by the way. It's much appreciated.

Zippy

Hi -

[quoted text clipped - 19 lines]

 

[Zippy]

You are right that there will be three options with Project A - Modified
Only, Un-Modified only and both. And Project B can be with Modified A or
alone.

Thanks again.

Zippy.

Hi -

Can you clarify the logic a bit?

If you select project "B", you ask if Modified project "A" is to be included
as well.

But if you select Project "A", are there three options - Modified Only, Un-
Modified only or both?

John

That works fine if I choose Project B. Is there a way to make it the same
question work with the Project A's ie Can I also choose to have "plain"
Project A and/or Modified Project A or would that need another query.

Thanks very much for help so far by the way. It's much appreciated.

Zippy

Hi -

[quoted text clipped - 19 lines]

Zippy