Check box not having a null value

[Christopher W via AccessMonster.com]

I am trying to get a query to search from a check box the problem is that a
check box cant be null it is either true or false. So what i whant it to do
is if the check bos is ticked, therefore TRUE. To check for records that are
true, However when it is not ticked i whant it to return all the records. I
have tried the following and riceve the error "At most one record can be
returned by the subquery". These three are basically exactly the same apart
from some small alterations "AND" to "OR" and instead of "-1 and 0" I split
it across two expressions; the last is just an nested IF statment that i
tryed and i got the same error again. But is there a better way to express it
that whant get the error?

(([forms]![multi query]![check60]=-1)=(select (basket) from transaction where
transaction.[Basket]= -1)) AND (([forms]![multi query]![check60]=0)=(select
(basket) from transaction where transaction.[Basket] = 0 or -1)))


(([forms]![multi query]![check60]=-1)=(select (basket) from transaction where
transaction.[Basket]= -1)) AND (([forms]![multi query]![check60]=0)=(select
(basket) from transaction where transaction.[Basket] = 0))) AND (([forms]!
[multi query]![check60]=0)=(select (basket) from transaction where
transaction.[Basket] = -1)))

(([forms]![multi query]![check60]=-1)=(select (basket) from transaction where
transaction.[Basket]= -1)) OR (([forms]![multi query]![check60]=0)=(select
(basket) from transaction where transaction.[Basket] = 0))) AND (([forms]!
[multi query]![check60]=0)=(select (basket) from transaction where
transaction.[Basket] = -1)))

IIF([forms]![multi query]![check60]=-1,-1,-1 OR 0)

 

[Allen Browne]

Set up the WHERE clause of your query so that the unticked check box returns
a True condition, or where your other condition is true.

This example returns all values if check60 is ticked, but only the values
where MyField = 7 if the box is not ticked:
SELECT * FROM Table1
WHERE ([forms]![multi query]![check60]=False) OR ([MyField] = 7);

Ultimately, this kind of thing starts to get unweildy and inefficient when
you have lots of phrases in the WHERE clause, so a better solution might be
to build the WhereCondition for the OpenReport action from only the fields
on your form that actually should be in the WhereCondition.

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

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

message
news:[email protected]...

 

[Christopher W via AccessMonster.com]

Mate you are an absolute genies
It would be embarrassing to admit how long I have been banging my head
against a wall with that one, it works fine.

If I could trouble you for the answer to another puzzling conundrum I have
been faced with.

I have been trying to develop a graph using Microsoft Graph 2000 Chart. I in
encountered errors when I tried running it from a query that used anything as
a criteria, i.e. a combo box or a text box etc; I posted this problem on this
site and received the instructions to insert the following in to my row
source column:

SELECT (Format([Date of Research],"MMM 'YY")) ,
Sum([New cost (Install)]) AS [Sum Of New cost (Install)]
FROM [GQ-IC]
WHERE [ANZIC] = [Forms]![Graph Selection]![combo13]
GROUP BY Year([Date of Research])*12 + Month([Date of Research])-1,
Format([Date of Research], "MMM 'YY");

Instead of:

TRANSFORM Sum([New cost (Install)]) AS [SumOfNew cost (Install)]
SELECT (Format([Date of Research],"MMM 'YY"))
FROM [GQ-GA-]
GROUP BY (Year([Date of Research])*12 + Month([Date of Research])-1),(Format(
[Date of Research],"MMM 'YY"))
PIVOT [Plant and Machinery ID];

However my problem is that I can now not get the graph to produce several
lines in the graph it groups all the data in together as the though it is one
(lets say) product but I need a line for each Product. I think that this
comes from the PIVOT section of the original row source code. But PIVOT is
not recognised when I add it to the new code provided.

Thank you so much for your reply I really appreciate it, I understand that
asking another question is not really the done thing so I will add this as a
new thread as well.

Thanks mate

Set up the WHERE clause of your query so that the unticked check box returns
a True condition, or where your other condition is true.

This example returns all values if check60 is ticked, but only the values
where MyField = 7 if the box is not ticked:
SELECT * FROM Table1
WHERE ([forms]![multi query]![check60]=False) OR ([MyField] = 7);

Ultimately, this kind of thing starts to get unweildy and inefficient when
you have lots of phrases in the WHERE clause, so a better solution might be
to build the WhereCondition for the OpenReport action from only the fields
on your form that actually should be in the WhereCondition.

I am trying to get a query to search from a check box the problem is that a
check box cant be null it is either true or false. So what i whant it to

[quoted text clipped - 35 lines]

IIF([forms]![multi query]![check60]=-1,-1,-1 OR 0)

 

[Allen Browne]

Probably best handled in the new thread.

Not sure if any of the field names are parameters, but if so, you must
declare them in the Crosstab query (Paramters on the Query menu, in query
design.)

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

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

message

Mate you are an absolute genies
It would be embarrassing to admit how long I have been banging my head
against a wall with that one, it works fine.

If I could trouble you for the answer to another puzzling conundrum I have
been faced with.

I have been trying to develop a graph using Microsoft Graph 2000 Chart. I
in
encountered errors when I tried running it from a query that used anything
as
a criteria, i.e. a combo box or a text box etc; I posted this problem on
this
site and received the instructions to insert the following in to my row
source column:

SELECT (Format([Date of Research],"MMM 'YY")) ,
Sum([New cost (Install)]) AS [Sum Of New cost (Install)]
FROM [GQ-IC]
WHERE [ANZIC] = [Forms]![Graph Selection]![combo13]
GROUP BY Year([Date of Research])*12 + Month([Date of Research])-1,
Format([Date of Research], "MMM 'YY");

Instead of:

TRANSFORM Sum([New cost (Install)]) AS [SumOfNew cost (Install)]
SELECT (Format([Date of Research],"MMM 'YY"))
FROM [GQ-GA-]
GROUP BY (Year([Date of Research])*12 + Month([Date of
Research])-1),(Format(
[Date of Research],"MMM 'YY"))
PIVOT [Plant and Machinery ID];

However my problem is that I can now not get the graph to produce several
lines in the graph it groups all the data in together as the though it is
one
(lets say) product but I need a line for each Product. I think that this
comes from the PIVOT section of the original row source code. But PIVOT is
not recognised when I add it to the new code provided.

Thank you so much for your reply I really appreciate it, I understand that
asking another question is not really the done thing so I will add this as
a
new thread as well.

Thanks mate

Set up the WHERE clause of your query so that the unticked check box
returns
a True condition, or where your other condition is true.

This example returns all values if check60 is ticked, but only the values
where MyField = 7 if the box is not ticked:
SELECT * FROM Table1
WHERE ([forms]![multi query]![check60]=False) OR ([MyField] = 7);

Ultimately, this kind of thing starts to get unweildy and inefficient when
you have lots of phrases in the WHERE clause, so a better solution might
be
to build the WhereCondition for the OpenReport action from only the fields
on your form that actually should be in the WhereCondition.

I am trying to get a query to search from a check box the problem is that
a
check box cant be null it is either true or false. So what i whant it to

[quoted text clipped - 35 lines]

IIF([forms]![multi query]![check60]=-1,-1,-1 OR 0)