Query Too Complex, Too many Characters?

Eka1618 · Oct 6, 2008

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both select
queries and that is also the record source for my report. I recently had to
add new criteria options to these queries and I am getting the error that the
expression is typed incorrectly or is too complex to be evaluated. My reports
ran fine untill I added the new criteria. I have double checked my spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these queries,
the two select queries are extremely long. I do not really expect anyone to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however they wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Eka1618 · Oct 6, 2008

Hello Again,

I do think my queries are tool long, however, I ran another test by taking a
different criteria out that had worked before the new criteria was added, and
it still did not generate a report. So now I believe my problem exists with
the new criteria that I added. I have checked to make sure that I have
written the statement as existing statements are written.

Here is a sample of the existing (that have worked):

.... AND (L_PLAT = [forms].[frmCustomResultReport].[cmbLPlat] OR
[forms].[frmCustomResultReport].[cmbLPlat] = "<all>")
AND (K_PLAT = [forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") ...

Here are examples of what I would like to add:

.... AND (L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>")

AND (K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR [forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") ...

I am trying to break this down for anyone that may beable to help me out...
Please let me know if you have any suggestions, Thank You!

Jerry Whittle · Oct 6, 2008

If you have criteria with multiple AND's and OR's, there might be a
fundemental problem with how your data stored in tables. In other words a
normalization problem.

Please post the entire SQL statement and maybe that will give us a better
idea of what you are attempting and how the data is stored in your tables.

Eka1618 · Oct 6, 2008

Hi Jerry,

I have the entire SQL listed for all 3 queries in my first post.

These queires become complex due to the way I have my data stored. I wanted
to combine tblLock, tblKey and tblLock_Key into one table. Because of all of
the fuctionality requirements of the DB, I had to seperate this information
into three tables. tblLock_Key actually contains the same fields as tblLock
and tblKey. I am nearing the end of my intership, I do not have much time
left to fix normalization problems. In any event... everything in my DB works
well, but it could be stored better.

I have recently thought of a way to combine this information and restructure
table relationship to meet all functionality requirement, however, since my
internship is ending, my boss does not want me to get involved with major
table changes until the current version is capable of doing everything they
need it to do. I am just about finished with what they need it to do, so I am
hoping to get problem such as this solved quickly, so that I am free to
redevelop the tables.

Here are the queires again, Thank you for your help, it is much appreciated!

qryCustomGeneralRPT:

SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryGeneralResultUnionRpt:

SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

david · Oct 7, 2008

Do the two individual select queries work?

The queries don't look too long to me.

Do the select queries look ok in the Access query design view?

Are any of the fields Memo fields?

(david)

Eka1618 said:
Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both
select
queries and that is also the record source for my report. I recently had
to
add new criteria options to these queries and I am getting the error that
the
expression is typed incorrectly or is too complex to be evaluated. My
reports
ran fine untill I added the new criteria. I have double checked my
spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these
queries,
the two select queries are extremely long. I do not really expect anyone
to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however they
wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

John Spencer · Oct 7, 2008

Because of the way Access handles criteria of the format
L_EWO_NO = [forms]![frmCustomResultReport]![cmbLEWO] OR
[forms]![frmCustomResultReport]![cmbLEWO] = "<all>"
in combination with other criteria, the criteria rapidly grows to a point
where you will get a query too complex error.

In some cases you can reduce the complexity by using different criteria.

If a field is never NULL you can use the following for a TEXT field
L_EWO_NO LIKE IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", "*",
[forms]![frmCustomResultReport]![cmbLEWO])

For a number field that is never null you can use a statement that sets a
range that would include all the possible values

L_EWO_NO Between IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>",
-999999999,[forms]![frmCustomResultReport]![cmbLEWO]) and
IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", 999999999,
[forms]![frmCustomResultReport]![cmbLEWO])

For a date field that is never null you can use the same range technique
L_EWO_NO Between IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>",
#100-1-1#,[forms]![frmCustomResultReport]![cmbLEWO]) and
IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", #3999/12/31#,
[forms]![frmCustomResultReport]![cmbLEWO])

Another choice is to build the query or query criteria on the fly using VBA.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both select
queries and that is also the record source for my report. I recently had to
add new criteria options to these queries and I am getting the error that the
expression is typed incorrectly or is too complex to be evaluated. My reports
ran fine untill I added the new criteria. I have double checked my spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these queries,
the two select queries are extremely long. I do not really expect anyone to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however they wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Eka1618 · Oct 7, 2008

Hi David,

The 2 select queries worked until I added the new criteria lines:

AND (L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>")

AND (K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR [forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>")

It does not let me view it in design view (It never has). However, it did
work fine until I added these new criteria lines.

There are no Memo fields being used in the criteria portion (my parameters).
In the recordset that I am trying to retrieve, there are some Memo Fields
being used. However, I have not had any problems trying to get these values.

I thought it was becoming long and so that's I why I wondered if it was the
size. It would be nice if I could break these queries down a bit and say
something like:

Where L_ID In(qryFindLID) AND K_ID In(qryFindKID)... If that makes any sense.

The queries seem complicated and I would like to simplify them before my
internship ends.

Please let me know if you have any more suggestions, Thank you for your help!

--
~Erica~

david said:
Do the two individual select queries work?

The queries don't look too long to me.

Do the select queries look ok in the Access query design view?

Are any of the fields Memo fields?

(david)

Eka1618 said:

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both
select
queries and that is also the record source for my report. I recently had
to
add new criteria options to these queries and I am getting the error that
the
expression is typed incorrectly or is too complex to be evaluated. My
reports
ran fine untill I added the new criteria. I have double checked my
spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these
queries,
the two select queries are extremely long. I do not really expect anyone
to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however they
wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Click to expand...

Eka1618 · Oct 7, 2008

Hello John,

Thank you for your suggestion. I would like to try some of these other
options you have mention before getting into VB coding. It definitely seems
like it would reduce the size of them a lot.

I was saying to David (above post) that it would be nice to break down the
select queries into smaller ones and say something like:

where L_ID IN(qryFindLID) and K_ID IN(qryFindKID)... then in qryFindLID &
qryFindKID, define all of the criteria. I hope that makes sense...

I am not sure if that would work, I have to try. In any event, I'd like to
go with your sugestion and see how that works out.

Thank again!

--
~Erica~

John Spencer said:
Because of the way Access handles criteria of the format
L_EWO_NO = [forms]![frmCustomResultReport]![cmbLEWO] OR
[forms]![frmCustomResultReport]![cmbLEWO] = "<all>"
in combination with other criteria, the criteria rapidly grows to a point
where you will get a query too complex error.

In some cases you can reduce the complexity by using different criteria.

If a field is never NULL you can use the following for a TEXT field
L_EWO_NO LIKE IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", "*",
[forms]![frmCustomResultReport]![cmbLEWO])

For a number field that is never null you can use a statement that sets a
range that would include all the possible values

L_EWO_NO Between IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>",
-999999999,[forms]![frmCustomResultReport]![cmbLEWO]) and
IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", 999999999,
[forms]![frmCustomResultReport]![cmbLEWO])

For a date field that is never null you can use the same range technique
L_EWO_NO Between IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>",
#100-1-1#,[forms]![frmCustomResultReport]![cmbLEWO]) and
IIF([forms]![frmCustomResultReport]![cmbLEWO]="<all>", #3999/12/31#,
[forms]![frmCustomResultReport]![cmbLEWO])

Another choice is to build the query or query criteria on the fly using VBA.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both select
queries and that is also the record source for my report. I recently had to
add new criteria options to these queries and I am getting the error that the
expression is typed incorrectly or is too complex to be evaluated. My reports
ran fine untill I added the new criteria. I have double checked my spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these queries,
the two select queries are extremely long. I do not really expect anyone to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however they wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Click to expand...

david · Oct 8, 2008

It's a lot easier to work with things in the query design grid,
and I agree, the first thing to do would be to break it down
into sections.

You've got a couple of sub-queries. These can be re-written
as named queries (and tested separately).

....
AND (tblResults.K_ID) IN subquery_1
AND (tblResults.PAT_NO) IN subquery_2
....

subquery1:
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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>")

Then remove the IN clause, and use a join instead:

select * from table1 inner join subquery1

I guess you haven't used joins because they are harder to write. After
you have broken out the subqueries, use the Access query designer to
design the joins.

(david)

Eka1618 said:
Hi David,

The 2 select queries worked until I added the new criteria lines:

AND (L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>")

AND (K_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR [forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>")

It does not let me view it in design view (It never has). However, it did
work fine until I added these new criteria lines.

There are no Memo fields being used in the criteria portion (my
parameters).
In the recordset that I am trying to retrieve, there are some Memo Fields
being used. However, I have not had any problems trying to get these
values.

I thought it was becoming long and so that's I why I wondered if it was
the
size. It would be nice if I could break these queries down a bit and say
something like:

Where L_ID In(qryFindLID) AND K_ID In(qryFindKID)... If that makes any
sense.

The queries seem complicated and I would like to simplify them before my
internship ends.

Please let me know if you have any more suggestions, Thank you for your
help!

--
~Erica~

david said:

Do the two individual select queries work?

The queries don't look too long to me.

Do the select queries look ok in the Access query design view?

Are any of the fields Memo fields?

(david)

Eka1618 said:

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both
select
queries and that is also the record source for my report. I recently
had
to
add new criteria options to these queries and I am getting the error
that
the
expression is typed incorrectly or is too complex to be evaluated. My
reports
ran fine untill I added the new criteria. I have double checked my
spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these
queries,
the two select queries are extremely long. I do not really expect
anyone
to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however
they
wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow]
OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow]
OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Click to expand...

Click to expand...

Eka1618 · Oct 8, 2008

Hi David,

That's exactly what I would like to do. I am going to work on this and
hopefully I can get it working...

Thanks again!
--
~Erica~

david said:
It's a lot easier to work with things in the query design grid,
and I agree, the first thing to do would be to break it down
into sections.

You've got a couple of sub-queries. These can be re-written
as named queries (and tested separately).

....
AND (tblResults.K_ID) IN subquery_1
AND (tblResults.PAT_NO) IN subquery_2
....

subquery1:
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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>")

Then remove the IN clause, and use a join instead:

select * from table1 inner join subquery1

I guess you haven't used joins because they are harder to write. After
you have broken out the subqueries, use the Access query designer to
design the joins.

(david)

Eka1618 said:

Hi David,

The 2 select queries worked until I added the new criteria lines:

AND (L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>")

AND (K_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR [forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>")

It does not let me view it in design view (It never has). However, it did
work fine until I added these new criteria lines.

There are no Memo fields being used in the criteria portion (my
parameters).
In the recordset that I am trying to retrieve, there are some Memo Fields
being used. However, I have not had any problems trying to get these
values.

I thought it was becoming long and so that's I why I wondered if it was
the
size. It would be nice if I could break these queries down a bit and say
something like:

Where L_ID In(qryFindLID) AND K_ID In(qryFindKID)... If that makes any
sense.

The queries seem complicated and I would like to simplify them before my
internship ends.

Please let me know if you have any more suggestions, Thank you for your
help!

--
~Erica~

david said:

Do the two individual select queries work?

The queries don't look too long to me.

Do the select queries look ok in the Access query design view?

Are any of the fields Memo fields?

(david)

Hello,

What I am trying to accomplish is to give the user the ability to run a
report based on different criteria from 4 different tables:

tblLock
tblKey
tblLock_Key
tblPattern

I have created 2 select queries and a union query that combines both
select
queries and that is also the record source for my report. I recently
had
to
add new criteria options to these queries and I am getting the error
that
the
expression is typed incorrectly or is too complex to be evaluated. My
reports
ran fine untill I added the new criteria. I have double checked my
spelling
and that seems to be ok, so I believe that my query is just too long.

I would like to hope that there is a simpler way to generate these
queries,
the two select queries are extremely long. I do not really expect
anyone
to
analyze this code for code; I am just looking to learn of a different
technique to use to enable my users to generate this report however
they
wish.

Here are the 3 queires:

qryGeneralResultUnionRPT:
SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS, L_PART_NO,
K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomGeneralRPT
UNION ALL SELECT Q_INC, Q_ID, SAMPLE_NO, OBSERVATIONS, TEST_RESULTS,
L_PART_NO, K_PART_NO, PAT_NO, PAT_SIZE, L_FAILURE_MODE, K_FAILURE_MODE
FROM qryCustomMultiGeneralRPT
ORDER BY qryCustomGeneralRPT.Q_INC;

qryCustomGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow]
OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

qryCustomMultiGeneralRPT:
SELECT tblResults.Q_INC, tblResults.Q_ID, tblResults.SAMPLE_NO,
tblResults.OBSERVATIONS, tblResults.TEST_RESULTS, tblResults.L_PART_NO,
tblResults.K_PART_NO, tblResults.PAT_NO, tblResults.PAT_SIZE,
tblResults.L_ID, tblResults.K_ID, tblResults.L_FAILURE_MODE,
tblResults.K_FAILURE_MODE
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH =
[forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow]
OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 = "GENERAL" );

If anyone has any suggestions, please let me know, Thank You!

Click to expand...

Click to expand...

Eka1618 · Oct 8, 2008

Hi David,

Well I think I may be close, but I am getting the following error:

The Specified firld 'PAT_NO' could refer to more that one table listed in
the From Clause of your SQL statement.

This way seems much better now and I will actually beable to get rid of some
other queries I wrote if I can get this to work...

I have broken down my queries like this:

Union Query that is record source to the report:
SELECT *
FROM qryCustomResults
UNION ALL SELECT *
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

qryCustomResults:
SELECT *
FROM ((tblResults INNER JOIN qryFindLID ON tblResults.L_ID =
qryFindLID.L_ID) INNER JOIN qryFindKID ON tblResults.K_ID = qryFindKID.K_ID)
INNER JOIN qryFindPattern ON (tblResults.PAT_SIZE = qryFindPattern.PAT_SIZE)
AND (tblResults.PAT_NO = qryFindPattern.PAT_NO)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryCustomResultsMulti:
SELECT *
FROM (tblResults INNER JOIN qryFindLKID ON tblResults.L_K_ID =
qryFindLKID.L_K_ID) INNER JOIN qryFindLKPattern ON (tblResults.PAT_NO =
qryFindLKPattern.PAT_NO) AND (tblResults.PAT_SIZE = qryFindLKPattern.PAT_SIZE)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryFindLID:
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>");

qryFindKID:
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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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 (K_HARD_VALUE_HIGH
= [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>");

qryFindPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

qryFindLKID:
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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND
(K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>");

qryFindLKPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblLKPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

Please let me know if you have any more suggestions, Thank You!

david · Oct 9, 2008

The Specified firld 'PAT_NO' could refer to more that one table listed

in the From Clause of your SQL statement.

I don't see where that is coming from either. Can you narrow it down
at all? Do you only see that error when you try the Union query?

(david)

Eka1618 said:
Hi David,

Well I think I may be close, but I am getting the following error:

The Specified firld 'PAT_NO' could refer to more that one table listed in
the From Clause of your SQL statement.

This way seems much better now and I will actually beable to get rid of
some
other queries I wrote if I can get this to work...

I have broken down my queries like this:

Union Query that is record source to the report:
SELECT *
FROM qryCustomResults
UNION ALL SELECT *
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

qryCustomResults:
SELECT *
FROM ((tblResults INNER JOIN qryFindLID ON tblResults.L_ID =
qryFindLID.L_ID) INNER JOIN qryFindKID ON tblResults.K_ID =
qryFindKID.K_ID)
INNER JOIN qryFindPattern ON (tblResults.PAT_SIZE =
qryFindPattern.PAT_SIZE)
AND (tblResults.PAT_NO = qryFindPattern.PAT_NO)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryCustomResultsMulti:
SELECT *
FROM (tblResults INNER JOIN qryFindLKID ON tblResults.L_K_ID =
qryFindLKID.L_K_ID) INNER JOIN qryFindLKPattern ON (tblResults.PAT_NO =
qryFindLKPattern.PAT_NO) AND (tblResults.PAT_SIZE =
qryFindLKPattern.PAT_SIZE)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryFindLID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>");

qryFindKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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
(K_HARD_VALUE_HIGH
= [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>");

qryFindPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

qryFindLKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND
(K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") AND (K_PLAT
=
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>");

qryFindLKPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblLKPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

Please let me know if you have any more suggestions, Thank You!

Lord Kelvan · Oct 9, 2008

dont do select *

if you do select * do

select tablename.*

Regards
Kelvan

Eka1618 · Oct 9, 2008

Hi David,

I both qryFindLID & qryFindLKID, if I try to run them, I get the error, data
mismatch in expression. qryFindKID, qryFindPattern, and qryFindLKPattern are
all the same format and they will run fine.

Here are the two queries again:

qryFindLID:

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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>");

qryFindLKID:

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_SCALE =
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE =
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND
(K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") AND (K_PLAT =
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>");

I have tried what Lord Kelvan suggested, but I think my Union query is not
going to work at this point since my subqueries are not working.

Please let me know what you think when you get a chance, Thank You!

--
~Erica~

david said:
The Specified firld 'PAT_NO' could refer to more that one table listed
in the From Clause of your SQL statement.

Click to expand...

I don't see where that is coming from either. Can you narrow it down
at all? Do you only see that error when you try the Union query?

(david)

Eka1618 said:

Hi David,

Well I think I may be close, but I am getting the following error:

The Specified firld 'PAT_NO' could refer to more that one table listed in
the From Clause of your SQL statement.

This way seems much better now and I will actually beable to get rid of
some
other queries I wrote if I can get this to work...

I have broken down my queries like this:

Union Query that is record source to the report:
SELECT *
FROM qryCustomResults
UNION ALL SELECT *
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

qryCustomResults:
SELECT *
FROM ((tblResults INNER JOIN qryFindLID ON tblResults.L_ID =
qryFindLID.L_ID) INNER JOIN qryFindKID ON tblResults.K_ID =
qryFindKID.K_ID)
INNER JOIN qryFindPattern ON (tblResults.PAT_SIZE =
qryFindPattern.PAT_SIZE)
AND (tblResults.PAT_NO = qryFindPattern.PAT_NO)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryCustomResultsMulti:
SELECT *
FROM (tblResults INNER JOIN qryFindLKID ON tblResults.L_K_ID =
qryFindLKID.L_K_ID) INNER JOIN qryFindLKPattern ON (tblResults.PAT_NO =
qryFindLKPattern.PAT_NO) AND (tblResults.PAT_SIZE =
qryFindLKPattern.PAT_SIZE)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryFindLID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>");

qryFindKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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
(K_HARD_VALUE_HIGH
= [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>");

qryFindPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

qryFindLKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND
(K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") AND (K_PLAT
=
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>");

qryFindLKPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblLKPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

Please let me know if you have any more suggestions, Thank You!

Click to expand...

Eka1618 · Oct 9, 2008

Thank you for your suggestion. I have changed it, but some of my subqueries
are not working correctly so I cannot tell if using tblName.* will work just
yet.

I have posted questions regarding my subqueires with my conversation with
David (above post). If you have anymore suggestions, please let me know,
Thank You!

Eka1618 · Oct 9, 2008

Hi David and Kalven,

I have modified my subqueries and they are all working! Hoever, My Union qry
is not working.

I have tried using the following, but it says that it does not recognize
'tblResults.*' as a valid field name:

SELECT tblResults.*
FROM qryCustomResults
UNION ALL SELECT tblResults.*
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

Honstly, I don't think the way I have it makes sense... I mean it makes
sense because in both of those queries, I have 'tblResults.*' listed in my
field values, but it doesnt make sense because I am saying FROM
qryCustomResults, not tblResults.

Also, If I say SELECT qryCustomResults.* for instance, it tells me that I do
not have the same number of fields.

Here are the two queires used in this Union:

qryCustomResults:

SELECT tblResults.*, *
FROM ((tblResults INNER JOIN qryFindLID ON tblResults.L_ID =
qryFindLID.L_ID) INNER JOIN qryFindKID ON tblResults.K_ID = qryFindKID.K_ID)
INNER JOIN qryFindPattern ON (tblResults.PAT_NO = qryFindPattern.PAT_NO) AND
(tblResults.PAT_SIZE = qryFindPattern.PAT_SIZE)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryCustomResultsMulti:

SELECT tblResults.*, *
FROM (tblResults INNER JOIN qryFindLKID ON tblResults.L_K_ID =
qryFindLKID.L_K_ID) INNER JOIN qryFindLKPattern ON (tblResults.PAT_SIZE =
qryFindLKPattern.PAT_SIZE) AND (tblResults.PAT_NO = qryFindLKPattern.PAT_NO)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

Please let me know if you have any other suggestions regarding my Union
Query. Thanks again for all the help!

--
~Erica~

david said:
The Specified firld 'PAT_NO' could refer to more that one table listed
in the From Clause of your SQL statement.

Click to expand...

I don't see where that is coming from either. Can you narrow it down
at all? Do you only see that error when you try the Union query?

(david)

Eka1618 said:

Hi David,

Well I think I may be close, but I am getting the following error:

The Specified firld 'PAT_NO' could refer to more that one table listed in
the From Clause of your SQL statement.

This way seems much better now and I will actually beable to get rid of
some
other queries I wrote if I can get this to work...

I have broken down my queries like this:

Union Query that is record source to the report:
SELECT *
FROM qryCustomResults
UNION ALL SELECT *
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

qryCustomResults:
SELECT *
FROM ((tblResults INNER JOIN qryFindLID ON tblResults.L_ID =
qryFindLID.L_ID) INNER JOIN qryFindKID ON tblResults.K_ID =
qryFindKID.K_ID)
INNER JOIN qryFindPattern ON (tblResults.PAT_SIZE =
qryFindPattern.PAT_SIZE)
AND (tblResults.PAT_NO = qryFindPattern.PAT_NO)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryCustomResultsMulti:
SELECT *
FROM (tblResults INNER JOIN qryFindLKID ON tblResults.L_K_ID =
qryFindLKID.L_K_ID) INNER JOIN qryFindLKPattern ON (tblResults.PAT_NO =
qryFindLKPattern.PAT_NO) AND (tblResults.PAT_SIZE =
qryFindLKPattern.PAT_SIZE)
WHERE (((tblResults.TEST_TYPE) Like
[forms].[frmCustomResultReport].[cmbTest]));

qryFindLID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<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
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<all>");

qryFindKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<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
(K_HARD_VALUE_HIGH
= [forms].[frmCustomResultReport].[cmbKHardValueHigh] OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>");

qryFindPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

qryFindLKID:
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_SCALE
=
[forms].[frmCustomResultReport].[cmbLHardScale] OR
[forms].[frmCustomResultReport].[cmbLHardScale] = "<all>") AND
(L_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbLHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbLHardValueHigh] = "<all>") AND
(L_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbLHardValueLow] OR
[forms].[frmCustomResultReport].[cmbLHardValueLow] = "<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_SCALE
=
[forms].[frmCustomResultReport].[cmbKHardScale] OR
[forms].[frmCustomResultReport].[cmbKHardScale] = "<all>") AND
(K_HARD_VALUE_HIGH = [forms].[frmCustomResultReport].[cmbKHardValueHigh]
OR
[forms].[frmCustomResultReport].[cmbKHardValueHigh] = "<all>") AND
(K_HARD_VALUE_LOW = [forms].[frmCustomResultReport].[cmbKHardValueLow] OR
[forms].[frmCustomResultReport].[cmbKHardValueLow] = "<all>") AND (K_PLAT
=
[forms].[frmCustomResultReport].[cmbKPlat] OR
[forms].[frmCustomResultReport].[cmbKPlat] = "<all>") AND (K_TOP_COAT =
[forms].[frmCustomResultReport].[cmbKCoat] OR
[forms].[frmCustomResultReport].[cmbKCoat] = "<all>");

qryFindLKPattern:
SELECT PAT_NO, PAT_SIZE
FROM tblLKPattern
WHERE ( PAT_NO = [forms].[frmCustomResultReport].[cmbPatNo] OR
[forms].[frmCustomResultReport].[cmbPatNo] = 0) AND (PAT_SIZE =
[forms].[frmCustomResultReport].[cmbPatSize] OR
[forms].[frmCustomResultReport].[cmbPatSize] = "<all>" );

Please let me know if you have any more suggestions, Thank You!

Click to expand...

Lord Kelvan · Oct 9, 2008

because tblresults isnt a table in qryCustomResults or in
qryCustomResultsMulti =P

try this

SELECT qryCustomResults.*
FROM qryCustomResults
UNION ALL SELECT qryCustomResultsMulti.*
FROM qryCustomResultsMulti
ORDER BY qryCustomResults.Q_INC;

Eka1618 · Oct 9, 2008

Kelvan,

I did try that before cause that made more sense, but no luck. I ended up
just adding all of the fields one - by - one. Now everything is working
perfectly as it did before

Thanks again for your help!

Lord Kelvan · Oct 9, 2008

yeah you shouldnt use select * with union but at least it is working
now

Regards
Kelvan

Using Multiple Combo Boxes as Criteria for Query	9	Jun 9, 2008
Explanation on Error Message	1	Jun 12, 2008

Query Too Complex, Too many Characters?

Eka1618

Eka1618

Jerry Whittle

Eka1618

david

John Spencer

Eka1618

Eka1618

david

Eka1618

Eka1618

david

Lord Kelvan

Eka1618

Eka1618

Eka1618

Lord Kelvan

Eka1618

Lord Kelvan

Ask a Question

Similar Threads