open a form with a union query

D

Dave

I am trying to open a form with a union query. I have set the recordsource
property of my form to the Union Query. I have created 2 listboxes that
provide input parameters to the queries in the Union. I have tested both
queries seperately with the listboxes to ensure I am getting the proper
results. This is what I do not know how to handle. I am using the openform
method because I need to return multiple input paramters for an IN statemant
for the where clauses of both queries. How do I handle the 2 seperate where
statements in the openform method?
Is this possible? Union Query below.

Thanks in ADVANCE!



SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA, [Bradley BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley
BOM's].ITEM_NO, [Bradley BOM's].PART, [Bradley BOM's].DESCR, [Bradley
BOM's].PCC, [Bradley BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley
BOM's].QPV, [Bradley BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley
BOM's].ECN_FROM, [Bradley BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley
BOM's].ECN_THRU, [Bradley BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley
BOM's].LTOFFSET, [Bradley BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR,
[Bradley BOM's].PNCATEGORY, [Bradley BOM's].FROM_ASSY, [Bradley
BOM's].EFFFROM_OV, [Bradley BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY,
[Bradley BOM's].PLANNER
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].WORKCENTER) Like forms!WorkCenterandNHAKit!List2)
And (([Bradley BOM's].EFFFROM_OV)<=Forms!Form1!Text77) And (([Bradley
BOM's].EFFTHRU_OV)>=Forms!Form1!Text79) And (([Bradley BOM's].TOP_BGEXPN) Is
Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley
BOM's].EFFFROM_OV)<=Forms!Form1!Text77) And (([Bradley BOM's].EFFTHRU_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].TOP_BGEXPN) Is Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley BOM's].EFFFROM_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].EFFTHRU_OV)>=Forms!Form1!Text79) And (([Bradley BOM's].TOP_BGEXPN) Is
Null) And ((IIf(forms!form1!list72 Is Null Or
forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is Null
Or forms!form1!list74=[Contract],1,0))=1)) Or ((([Bradley BOM's].WORKCENTER)
Like forms!WorkCenterandNHAKit!List2) And (([Bradley BOM's].EFFFROM_OV)
Between Forms!Form1!Text77 And Forms!Form1!Text79) And (([Bradley
BOM's].EFFTHRU_OV) Between Forms!Form1!Text77 And Forms!Form1!Text79) And
(([Bradley BOM's].TOP_BGEXPN) Is Null) And ((IIf(forms!form1!list72 Is Null
Or forms!form1!list72=[End_Item],1,0))=1) And ((IIf(forms!form1!list74 Is
Null Or forms!form1!list74=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley BOM's].END_ITEM

UNION

SELECT [Bradley BOM's].CONTRACT,
[Bradley BOM's].END_ITEM,
[Bradley BOM's].NHA,
[Bradley BOM's].NHA_PCC,
[Bradley BOM's].NHA_SRCPLT,
[Bradley BOM's].ITEM_NO,
[Bradley BOM's].PART,
[Bradley BOM's].DESCR,
[Bradley BOM's].PCC,
[Bradley BOM's].SOURCEPLT,
[Bradley BOM's].QTY_PER,
[Bradley BOM's].QPV,
[Bradley BOM's].ISSUE_UOM,
[Bradley BOM's].PUR_UOM,
[Bradley BOM's].ECN_FROM,
[Bradley BOM's].EFF_FROM,
[Bradley BOM's].EFF_THRU,
[Bradley BOM's].ECN_THRU,
[Bradley BOM's].WORKCENTER,
[Bradley BOM's].OPER,
[Bradley BOM's].LTOFFSET,
[Bradley BOM's].POSTDEDUCT,
[Bradley BOM's].RTG_PRIOR,
[Bradley BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY,
[Bradley BOM's].EFFFROM_OV,
[Bradley BOM's].EFFTHRU_OV,
[Bradley BOM's].THRU_ASSY,
[Bradley BOM's].PLANNER

FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) Like forms!WorkCenterandNHAKit!List0) AND
(([Bradley BOM's].EFFFROM_OV)<=[Forms]![Form1]![Text77]) AND (([Bradley
BOM's].EFFTHRU_OV)>=[Forms]![Form1]![Text79]) AND (([Bradley
BOM's].TOP_BGEXPN) Is Null) AND ((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley
BOM's].EFFFROM_OV)<=[Forms]![Form1]![Text77]) AND (([Bradley
BOM's].EFFTHRU_OV) Between [Forms]![Form1]![Text77] And
[Forms]![Form1]![Text79]) AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley BOM's].EFFFROM_OV) Between
[Forms]![Form1]![Text77] And [Forms]![Form1]![Text79]) AND (([Bradley
BOM's].EFFTHRU_OV)>=[Forms]![Form1]![Text79]) AND (([Bradley
BOM's].TOP_BGEXPN) Is Null) AND ((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1)) OR ((([Bradley BOM's].NHA) Like
forms!WorkCenterandNHAKit!List0) AND (([Bradley BOM's].EFFFROM_OV) Between
[Forms]![Form1]![Text77] And [Forms]![Form1]![Text79]) AND (([Bradley
BOM's].EFFTHRU_OV) Between [Forms]![Form1]![Text77] And
[Forms]![Form1]![Text79]) AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley BOM's].END_ITEM;
 

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

using values from a simple listbox to create a SQL IN Statement 3
Using And/Or with Check Box 2
Problem with a form, a listbox, an sql query and a subroutine 1
Query Criteria: evaluating criteria as a group 3
Problem with Query Criteria by IIF/Switch 1
List Box Paramater query with Null value 3
How to call a form with a control as parameter 1
Go to a new record on another form? 6

Top