A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Reload this Page Query Too Complex, Too many Characters?
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query Too Complex, Too many Characters?


« Previous Thread | Next Thread »

 
Page 1 of 2 1 2
 
Thread Tools Display Modes
  #1  
Old October 6th, 2008, 07:42 PM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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!
--
~Erica~
  #2  
Old October 6th, 2008, 08:01 PM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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!

--
~Erica~
  #3  
Old October 6th, 2008, 08:41 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
  
Posts: 4,732
Default Query Too Complex, Too many Characters?
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Eka1618" wrote:

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!

--
~Erica~
  #4  
Old October 6th, 2008, 09:23 PM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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;


--
~Erica~
  #5  
Old October 7th, 2008, 11:18 AM posted to microsoft.public.access.queries
david
external usenet poster
  
Posts: 398
Default Query Too Complex, Too many Characters?
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" wrote in message
...
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!
--
~Erica~


  #6  
Old October 7th, 2008, 12:49 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
  
Posts: 7,815
Default Query Too Complex, Too many Characters?
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

Eka1618 wrote:
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!
  #7  
Old October 7th, 2008, 01:11 PM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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" wrote:

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" wrote in message
...
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!
--
~Erica~



  #8  
Old October 7th, 2008, 01:19 PM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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" wrote:

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

Eka1618 wrote:
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!
  #9  
Old October 8th, 2008, 03:40 AM posted to microsoft.public.access.queries
david
external usenet poster
  
Posts: 398
Default Query Too Complex, Too many Characters?
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" wrote in message
...
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" wrote:

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" wrote in message
...
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!
--
~Erica~
  #10  
Old October 8th, 2008, 11:23 AM posted to microsoft.public.access.queries
Eka1618
external usenet poster
  
Posts: 90
Default Query Too Complex, Too many Characters?
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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!
--
~Erica~
 
Page 1 of 2 1 2



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:32 PM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.