limitations to access where clause conditions 1

[ringadeal]

Are there limitations to the amount of conditions that there can be in the where clause of an access query? When my where clause contains 31 conditions, an error message displays stating that the expression is too complex. This does not occur when the identical query contains less conditions.

 

[Remou]

You can have only 99 ANDs in a Where or Having clause:

http://www.databasedev.co.uk/access_specifications.html

It is often a good idea to post SQL. There are some very sharp SQL people visit this forum.

 

[ringadeal]

Your response was perfect!

 

[ringadeal]

Which Access limitation is violated by this query and that is not violated by the second one. The only difference that lies between the two queries is that the second query lists more product model numbers in the where clause (as I am not quite familiar with the terms listed on the linked webpage regarding Access limitations)

SELECT Query1.SoldToCMAccountNo, Max(Query1.DocID) AS MaxDocID, 
Query1.ShipToCompany, Query1.CustomText10, First(Query1.CustomText12) As 
FCustomText12, Query1.SoldToContact, Query1.ShipToAddress1, 
Query1.ShipToAddress2, Query1.ShipToCity, Query1.ShipToState, 
Query1.ShipToPostalCode, Query1.ShipToCountry, First(Query1.SoldToPhone) As 
FSoldToPhone, Query1.SoldToPhoneExt, Query1.LATITUDE, Query1.LONGITUDE, 
First(Query1.ManufacturerPartNumber) AS FManufacturerPartNumber, 
First(Query1.prodfamid_fam) AS Fprodfamid_fam FROM Query1 WHERE LATITUDE <= 
40.8210861895984 AND latitude >= 40.3865898104016 AND longitude >= 
-74.2737786508004 AND longitude <= -73.7014913491996 AND 
(((((((((((((((((((((((((((((ManufacturerPartNumber = '3408H')) OR 
((ManufacturerPartNumber = '4608H'))) OR ((ManufacturerPartNumber = 
'4616H'))) OR ((ManufacturerPartNumber = '4624H'))) OR 
((ManufacturerPartNumber = '4808H'))) OR ((ManufacturerPartNumber = 
'3416H'))) OR ((ManufacturerPartNumber = '3424H'))) OR 
((ManufacturerPartNumber = '3608H'))) OR ((ManufacturerPartNumber = 
'3616H'))) OR ((ManufacturerPartNumber = '3624H'))) OR 
((ManufacturerPartNumber = '3632H'))) OR ((ManufacturerPartNumber = 
'3808H'))) OR ((ManufacturerPartNumber = '3816H'))) OR 
((ManufacturerPartNumber = '3824H'))) OR ((ManufacturerPartNumber = 
'3832H'))) OR ((ManufacturerPartNumber = '3840H'))) OR 
((ManufacturerPartNumber = '4408H'))) OR ((ManufacturerPartNumber = 
'4416H'))) OR ((ManufacturerPartNumber = '4424H'))) OR 
((ManufacturerPartNumber = '4816H'))) OR ((ManufacturerPartNumber = 
'4824H'))) OR ((ManufacturerPartNumber = '4832H'))) OR 
((ManufacturerPartNumber = '4840H'))) OR ((ManufacturerPartNumber = 
'4816P'))) OR ((ManufacturerPartNumber = '3616P'))) OR 
((ManufacturerPartNumber = '3808C'))) OR ((ManufacturerPartNumber = 
'3816C'))) OR ((ManufacturerPartNumber = '3824C '))) GROUP BY 
Query1.SoldToCMAccountNo, Query1.ShipToCompany, Query1.CustomText10, 
Query1.SoldToContact, Query1.ShipToAddress1, Query1.ShipToAddress2, 
Query1.ShipToCity, Query1.ShipToState, Query1.ShipToPostalCode, 
Query1.ShipToCountry, Query1.SoldToPhoneExt, Query1.LATITUDE, 
Query1.LONGITUDE ORDER BY Max(Query1.DocID) DESC

Second Query:

SELECT Query1.SoldToCMAccountNo, Max(Query1.DocID) AS MaxDocID, 
Query1.ShipToCompany, Query1.CustomText10, First(Query1.CustomText12) As 
FCustomText12, Query1.SoldToContact, Query1.ShipToAddress1, 
Query1.ShipToAddress2, Query1.ShipToCity, Query1.ShipToState, 
Query1.ShipToPostalCode, Query1.ShipToCountry, First(Query1.SoldToPhone) As 
FSoldToPhone, Query1.SoldToPhoneExt, Query1.LATITUDE, Query1.LONGITUDE, 
First(Query1.ManufacturerPartNumber) AS FManufacturerPartNumber, 
First(Query1.prodfamid_fam) AS Fprodfamid_fam FROM Query1 WHERE LATITUDE <= 
40.8210861895984 AND latitude >= 40.3865898104016 AND longitude >= 
-74.2737786508004 AND longitude <= -73.7014913491996 AND 
((((((ManufacturerPartNumber = '9264')) OR ((ManufacturerPartNumber = 
'9276'))) OR ((ManufacturerPartNumber = '9288'))) OR 
((ManufacturerPartNumber = '9200'))) OR ((ManufacturerPartNumber = 'C9264 
'))) GROUP BY Query1.SoldToCMAccountNo, Query1.ShipToCompany, 
Query1.CustomText10, Query1.SoldToContact, Query1.ShipToAddress1, 
Query1.ShipToAddress2, Query1.ShipToCity, Query1.ShipToState, 
Query1.ShipToPostalCode, Query1.ShipToCountry, Query1.SoldToPhoneExt, 
Query1.LATITUDE, Query1.LONGITUDE ORDER BY Max(Query1.DocID) DESC

 

[lespaul]

Without going through the entire query, my first suggestion is the IN operator:

WHERE ManufacturerPartNumber IN ('3408H', '4608H', '4616H', ..., '3824C')

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Remou]

Change this:

(((((((((((((((((((((((((((((ManufacturerPartNumber = '3408H')) OR
((ManufacturerPartNumber = '4608H'))) OR ((ManufacturerPartNumber =
'4616H'))) OR ((ManufacturerPartNumber = '4624H'))) OR
((ManufacturerPartNumber = '4808H'))) OR ((ManufacturerPartNumber =
'3416H'))) OR ((ManufacturerPartNumber = '3424H'))) OR
((ManufacturerPartNumber = '3608H'))) OR ((ManufacturerPartNumber =
'3616H'))) OR ((ManufacturerPartNumber = '3624H'))) OR
((ManufacturerPartNumber = '3632H'))) OR ((ManufacturerPartNumber =
'3808H'))) OR ((ManufacturerPartNumber = '3816H'))) OR
((ManufacturerPartNumber = '3824H'))) OR ((ManufacturerPartNumber =
'3832H'))) OR ((ManufacturerPartNumber = '3840H'))) OR
((ManufacturerPartNumber = '4408H'))) OR ((ManufacturerPartNumber =
'4416H'))) OR ((ManufacturerPartNumber = '4424H'))) OR
((ManufacturerPartNumber = '4816H'))) OR ((ManufacturerPartNumber =
'4824H'))) OR ((ManufacturerPartNumber = '4832H'))) OR
((ManufacturerPartNumber = '4840H'))) OR ((ManufacturerPartNumber =
'4816P'))) OR ((ManufacturerPartNumber = '3616P'))) OR
((ManufacturerPartNumber = '3808C'))) OR ((ManufacturerPartNumber =
'3816C'))) OR ((ManufacturerPartNumber = '3824C ')))

To:

ManufacturerPartNumber IN ('3408H','4608H','4616H','4624H',
'4808H','3416H',
'3424H', '3608H','3616H','3624H','3632H','3808H','3816H',
'3824H','3832H','3840H','4408H', '4416H','4424H','4816H',
'4824H','4832H','4840H','4816P','3616P', '3808C','3816C',
'3824C')


But take out the carriage returns first.

 

[dhookom]

I would not maintain an expression like this in any query. Manufacturer Part Numbers are not carved in stone. They will change and your list will need to be maintained. Data belongs in your tables -- not in your code

I would have a field in a table of partnumbers or possibly a table containing only these partnumbers. If you don't want to use data, at least create a small user-defined function that identifies if a partnumber is in the list. This would allow storing this "data" in a single place rather than in a complex expression.

Duane MS Access MVP

 

[ringadeal]

You are right, dhookom, the data is not hard coded in the query but rather dynamically generated.

This is the current problem that I face:

Based on the above suggestions to change the query syntax to resemble WHERE ManufacturerPartNumber IN ('3408H', '4608H', '4616H', ..., '3824C'), I had the syntax changed but the syntax switches back to the problematic syntax at runtime causing the error message again. I will explain.

This code generates a list of all part numbers (later to be dynamically inserted in the sql statement:

If (NOT Recordset2.BOF) Then
 Recordset2.MoveFirst 
i = 0 
varPartNumber = "" 
While (NOT Recordset2.EOF) 
if i = 0 Then 
varPartNumber = "'" & Recordset2.Fields.Item("ManufacturerPartNumber").Value & "'" 
Else 
varPartNumber = varPartNumber & "|'" & Recordset2.Fields.Item("ManufacturerPartNumber").Value & "'" 
End if 
Recordset2.MoveNext() 
i = i+1 
Wend 
 
Recordset2.MoveFirst 
End If

This is the recordset where clause:

WHERE 
If (varPartNumber <> "") Then
  Recordset1.Source = Recordset1.Source & " AND ManufacturerPartNumber IN 
(" & Replace(varPartNumber,"|", ", ") & ")"
End If

Respone.Write recordset code which illustrates what the actual sql query is:

WHERE ManufacturerPartNumber IN ('3408H', '4608H', '4616H', '4624H', 
'4808H', '3416H', '3424H', '3608H', '3616H', '3624H', '3632H', '3808H', 
'3816H', '3824H', '3832H', '3840H', '4408H', '4416H', '4424H', '4816H', 
'4824H', '4832H', '4840H', '4816P', '3616P', '3808C', '3816C', '3824C')

Resulting error message makes it evident that the syntax is converted to original (problematic) syntax at runtime:

[Microsoft][ODBC Microsoft Access Driver] Expression too complex in query 
expression 'AND (((((((((((((((((((((((((((((ManufacturerPartNumber = 
''3408H'')) OR ((ManufacturerPartNumber = '.

How is this issue resolved?

 

[HarleyQuinn]

Respone.Write? I'm hazarding a guess hat this isn't Access and VBA...?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Plus, please show us your code that results in the error, it's difficult to determine from the snippets.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[ringadeal]

Sorry, I guess I'm not being clear that this code is webpage code but since the issue is Access related, I'm posting it on an Access forum.

Page code is pretty long, is there any particular webpage code that will help troubleshoot the issue?

 

[Remou]

Any bits that mention ManufacturerPartNumber.