Argument Error in Union Query

[Eka1618]

Hello,

I am trying to execute a Union query based on two queries. It is giving me
an error that says: Expression in Argument 2 has Invalid Value.

Here is my Union Query (qryTorqueResultUnionRPT):

SELECT Q_INC, Q_ID, SAMPLE_NO, TENSION, NOTES, OBSERVATIONS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, PICTURE
FROM qryCustomTorqueRPT
UNION
SELECT Q_INC, Q_ID, SAMPLE_NO, TENSION, NOTES, OBSERVATIONS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, PICTURE
FROM qryCustomMultiTorqueRPT
ORDER BY qryCustomTorqueRpt.Q_INC;


The other two queries work perfectly and give me the correct results. I do
not understand what argument 2 is; Q_ID in the first SELECT statement or is
it the 2nd SELECT statement alltogether?


If anyone knows what to do here please let me know, Thank you!

~Erica~

 

[Eka1618]

Well, my other queries do not work now... I don't really understand why
though because They worked perfectly until I made the union query.

Brace yourself, My queries are long...

qryCustomTorqueRPT:

SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.TENSION, tblResults.NOTES, tblResults.OBSERVATIONS,
tblResults.L_PART_NO, tblResults.K_PART_NO, tblResults.PAT_NO,
tblResults.PAT_SIZE, tblResults.PICTURE
FROM tblResults
WHERE ((tblResults.L_ID)
In (select L_ID from tblLock
Where (L_EWO_NO = [forms].[frmCustomResultReport].[cmbLEWO] OR
[forms].[frmCustomResultReport].[cmbLEWO] = "<all>") AND (L_PART_NO =
[forms].[frmCustomResultReport].[cmbLPart] OR
[forms].[frmCustomResultReport].[cmbLPart] = "<all>") AND (L_SKID_NO =
[forms].[frmCustomResultReport].[cmbLSKID] OR
[forms].[frmCustomResultReport].[cmbLSKID] = "<all>") AND (L_MAT_TYPE =
[forms].[frmCustomResultReport].[cmbLMatType] OR
[forms].[frmCustomResultReport].[cmbLMatType] = "<all>") AND (L_MAT_HEAT =
[forms].[frmCustomResultReport].[cmbLMatHeat] OR
[forms].[frmCustomResultReport].[cmbLMatHeat] = "<all>") AND (L_HARD =
[forms].[frmCustomResultReport].[cmbLHard] OR
[forms].[frmCustomResultReport].[cmbLHard] = "<all>") AND (L_PLAT =
[forms].[frmCustomResultReport].[cmbLPlat] OR
[forms].[frmCustomResultReport].[cmbLPlat] = "<all>") AND (L_TOP_COAT =
[forms].[frmCustomResultReport].[cmbLCoat] OR
[forms].[frmCustomResultReport].[cmbLCoat] = "<all>") AND (L_THREAD =
[forms].[frmCustomResultReport].[cmbLThread] OR
[forms].[frmCustomResultReport].[cmbLThread] = "<all>") )
AND (tblResults.K_ID) IN (Select K_ID from tblKey Where (K_EWO_NO =
[forms].[frmCustomResultReport].[cmbKEWO] OR
[forms].[frmCustomResultReport].[cmbKEWO] = "<all>") AND (K_PART_NO =
[forms].[frmCustomResultReport].[cmbKPart] OR
[forms].[frmCustomResultReport].[cmbKPart] = "<all>") AND (K_SKID_NO =
[forms].[frmCustomResultReport].[cmbKSKID] OR
[forms].[frmCustomResultReport].[cmbKSKID] = "<all>") AND (K_MAT_TYPE =
[forms].[frmCustomResultReport].[cmbKMatType] OR
[forms].[frmCustomResultReport].[cmbKMatType] = "<all>") AND (K_MAT_HEAT =
[forms].[frmCustomResultReport].[cmbKMatHeat] OR
[forms].[frmCustomResultReport].[cmbKMatHeat] = "<all>") AND (K_HARD =
[forms].[frmCustomResultReport].[cmbKHard] OR
[forms].[frmCustomResultReport].[cmbKHard] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>") )
AND (tblResults.PAT_NO) IN (Select PAT_NO from tblPattern Where ( PAT_NO =
[forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) )
AND (tblResults.PAT_SIZE) IN (Select PAT_SIZE from tblPattern Where
(PAT_SIZE = [forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" ))
AND tblResults.TEST_TYPE = "TORQUE TENSION" );


qryCustomMultiTorqueRPT:

SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.TENSION, tblResults.NOTES, tblResults.OBSERVATIONS,
tblResults.L_PART_NO, tblResults.K_PART_NO, tblResults.PAT_NO,
tblResults.PAT_SIZE, tblResults.PICTURE
FROM tblResults
WHERE ((tblResults.L_K_ID)
In (select L_K_ID from tblLock_Key
Where (L_EWO_NO = [forms].[frmCustomResultReport].[cmbLEWO] OR
[forms].[frmCustomResultReport].[cmbLEWO] = "<all>") AND (L_PART_NO =
[forms].[frmCustomResultReport].[cmbLPart] OR
[forms].[frmCustomResultReport].[cmbLPart] = "<all>") AND (L_SKID_NO =
[forms].[frmCustomResultReport].[cmbLSKID] OR
[forms].[frmCustomResultReport].[cmbLSKID] = "<all>") AND (L_MAT_TYPE =
[forms].[frmCustomResultReport].[cmbLMatType] OR
[forms].[frmCustomResultReport].[cmbLMatType] = "<all>") AND (L_MAT_HEAT =
[forms].[frmCustomResultReport].[cmbLMatHeat] OR
[forms].[frmCustomResultReport].[cmbLMatHeat] = "<all>") AND (L_HARD =
[forms].[frmCustomResultReport].[cmbLHard] OR
[forms].[frmCustomResultReport].[cmbLHard] = "<all>") AND (L_PLAT =
[forms].[frmCustomResultReport].[cmbLPlat] OR
[forms].[frmCustomResultReport].[cmbLPlat] = "<all>") AND (L_TOP_COAT =
[forms].[frmCustomResultReport].[cmbLCoat] OR
[forms].[frmCustomResultReport].[cmbLCoat] = "<all>") AND (L_THREAD =
[forms].[frmCustomResultReport].[cmbLThread] OR
[forms].[frmCustomResultReport].[cmbLThread] = "<all>") AND (K_EWO_NO =
[forms].[frmCustomResultReport].[cmbKEWO] OR
[forms].[frmCustomResultReport].[cmbKEWO] = "<all>") AND (K_PART_NO =
[forms].[frmCustomResultReport].[cmbKPart] OR
[forms].[frmCustomResultReport].[cmbKPart] = "<all>") AND (K_SKID_NO =
[forms].[frmCustomResultReport].[cmbKSKID] OR
[forms].[frmCustomResultReport].[cmbKSKID] = "<all>") AND (K_MAT_TYPE =
[forms].[frmCustomResultReport].[cmbKMatType] OR
[forms].[frmCustomResultReport].[cmbKMatType] = "<all>") AND (K_MAT_HEAT =
[forms].[frmCustomResultReport].[cmbKMatHeat] OR
[forms].[frmCustomResultReport].[cmbKMatHeat] = "<all>") AND (K_HARD =
[forms].[frmCustomResultReport].[cmbKHard] OR
[forms].[frmCustomResultReport].[cmbKHard] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>") )
AND (tblResults.PAT_NO) IN (Select PAT_NO from tblLKPattern Where ( PAT_NO =
[forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) )
AND (tblResults.PAT_SIZE) IN (Select PAT_SIZE from tblLKPattern Where
(PAT_SIZE = [forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" ))
AND tblResults.TEST_TYPE = "TORQUE TENSION" );


IF anyone knows what I can do, please please let me know, Thank You!

~Erica~

 

[KARL DEWEY]

Place the ORDER BY in the first query, not the last.

 

[Eka1618]

Karl,

That is not working.... As soon as I run the union query, my opther two
queries become corrupt or something. I don't understand what is wrong...

Any other ideas?

~Erica~

 

[KARL DEWEY]

As soon as I run the union query, my opther two queries become corrupt or
something.
What has the other two queries to do with the union query?

 

[Eka1618]

I am trying to combine te results from both queries....

My other queries were working fine, then why i try to run the union query
that combines the two, I get this stupid error message about argument 2 being
invalid. However, that message now appears in the queries that I tried to
combine through the union. so now, If I were two run all three seperatly, I
get the same error message in each. The original queries did not have that
error until I ran the union query...

~Erica~

 

[KARL DEWEY]

I never heard of a union query messing up the queries it is combining.
I suggest you make copies of the queries and start taking them appart until
you lose the error so you will know what is causing it. Then you can work on
what is wrong.

 

[Eka1618]

That's what I have been doing.... this only occurs when I try to run the
union query... Like I said, my other queries worked fine when I tested them
(several times). I am just not sure what is wrong.

The only other thing I didn't mention was the union queries that are in all
of the combo boxes on the form that the queries get their parameters from. I
do not think this is the problem though because my queries worked fine befor
i made the union query.

Do you have any idea what the error message means though?

~Erica~

 

[Eka1618]

Ok I figured it out!

Soo... I still dont know why the error message occured, but it happened
because I was opening the query like this:

DoCmd.OpenQuery "qryTorqueResultUnionRPT", acViewReport

Now, when I am typing in the code in VB, the list includes acViewReport. Why
does it include it if it's going to cause problems. I do not know that much
about acViewReport when you run a query, so I'm sure that it is used, but
obviously for some other reason.

In any event, my query works fine now

Thanks for the help Karl!

~Erica~

 

[Stephen K. Young]

Place the ORDER BY in the first query, not the last.

--

I think the ORDER BY should be placed at the end of the union query. It is
only the field names or aliases that derive from the first query.

I ran a quick test to confirm this. And the Access 2003 help states the
following for Union queries:

Use aliases only in the first SELECT statement because they are ignored in
any others. In the ORDER BY clause, refer to fields by what they are called
in the first SELECT statement.

.... You can use an ORDER BY clause at the end of the last query argument to
display the returned data in a specified order.

Usually, because the field names are not always consistent between the
different queries, I put something like Order By 1,2,3 at the very end of
the last query in the Union, to order by the first three fields.

- Steve