Union query with if option button is true/false

[anchamal77]

i'm trying to create a query (union query) if i'm right.
i want to display three kinds of information based on the sellection of option buttons in form "mainmenu"

based on the query below
if materialtrue is true only part 1 is working fine
if shapetrue is true only part 2 is working fine

but if "materialtrue" and "shapetrue" are true is not working (it should display information that meet both conditions)


how do i do this?


SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE


FROM ITEMS
WHERE (((ITEMS.MATERIAL)=IIf([FORMS]![MAINMENU].[MATERIALTRUE]=-1,[Forms]![MAINMENU].[MATERIAL])));


UNION ALL SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=IIf([FORMS]![MAINMENU].[SHAPETRUE]=-1,[Forms]![MAINMENU].[SHAPE])));


UNION ALL SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=IIf([FORMS]![MAINMENU].[SHAPETRUE]=-1,[Forms]![MAINMENU].[SHAPE])) AND ((ITEMS.MATERIAL)=IIf([FORMS]![MAINMENU].[MATERIALTRUE]=-1,[Forms]![MAINMENU].[MATERIAL])));

[ranman256]

put each query into its own individual query.
instead of IIF statement, turn it into a CRITERIA and only get those items.
Q1 for the TRUE criteria
Q2 for the FALSE criteria
Then the union is :

select * from Q1
union

select * from Q2
union
select * from Q3

this way you can test each query to make it work. Then they all will work.

[anchamal77]

Tried like this but still not showing correct info

SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=[Forms]![MAINMENU].[SHAPE]));
UNION SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.MATERIAL)=[Forms]![MAINMENU].[MATERIAL]));
UNION SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=[Forms]![MAINMENU].[SHAPE]) AND ((ITEMS.MATERIAL)=[Forms]![MAINMENU].[MATERIAL]));

[anchamal77]

here is first query called "QUERY10"
SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.MATERIAL)=[Forms]![MAINMENU].[MATERIAL]));

second query called "query8"
SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=[Forms]![MAINMENU].[SHAPE]));

and the third query which should desplay joined info called "QUERY7"
SELECT ITEMS.ITEMCODE, ITEMS.NEWSUBCATEGORY1, ITEMS.SHAPE, ITEMS.MATERIAL, ITEMS.COLOR, ITEMS.SIZE
FROM ITEMS
WHERE (((ITEMS.SHAPE)=Forms!MAINMENU.SHAPE) And ((ITEMS.MATERIAL)=Forms!MAINMENU.MATERIAL));

now when i join:
SELECT * from query8
union
select * from query10
UNION select * from query7;

but still no success

[Minty]

I'm not sure a Union query is the way to go here.

I think you simply want something like

Select * FROM ITEMS 
WHERE (MATERIAL = [Forms]![MAINMENU].[MATERIAL] OR[Forms]![MAINMENU].[MATERIAL] Is Null) 
AND (SHAPE = [Forms]![MAINMENU].[SHAPE] OR [Forms]![MAINMENU].[SHAPE] is Null)