thanks for your response. I appreciate your assistance as I am a
copy-and-paste-coder and tend to run into trouble every so often!
Below are 3 code snips: The code which generates the string of
ManufacturerPartNumbers, the recordset query code and the web assist server
bahavior code. Only applicable code is included.
This code creates the string:
<%
'LOOP CODE SUPPLIED BY WEBASSIST TECH SUPPORT TO CREATE A STRING OF ALL THE
Products.ManufacturerPartNumber VALUES WITH A VERT BAR SEPARATING EACH ONE IN
ORDER FOR THE ServerBehavior TO COMPARE THE VALUES TO THE
ManufacturerPartNumber FIELD PROPERLY WHEN THERE IS NORMALLY JUST ONE FIELD
VALUE. ALSO REQUIRED TO CHANGE Recordset2__MMColParam = "" TO
Recordset2__MMColParam = "-1" AND ALSO REFER TO THE SB WIZARD SETTINGS THAT
CORRESPOND TO THIS CODE TO COMPLETE THE FUNCTIONALITY
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
'END OF WEBASSIST TECH SUPPORT SUPPLIED CODE
%>
This is a snip of the applicable WebAssist Server Behavior Generated Code:
WADbSearch1_whereClause=BuildKeyword(WADbSearch1_KeyArr1,"" &
cStr(varPartNumber) & "
","AND","=",",%20","|","%22","%22",0,WADbSearch1_wildCard,WADbSearch1_dateSeparator,WADbSearch1_whereClause)
This is the applicable recordset code:
Recordset1.Source = "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,
F(Query1.SoldToPhone) As SoldToPhone, Query1.SoldToPhoneExt, Query1.LATITUDE,
Query1.LONGITUDE, First(Query1.ManufacturerPartNumber) AS
FManufacturerPartNumber, First(Query1.prodfamid_fam) AS Fprodfamid_fam FROM
Query1 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"
End If
setQueryBuilderSource Recordset1, WADbSearch1_whereClause, false
John W. Vinson said:
Page code creates a string of all ManufacturerPartNumbers that share a common
ProductFamilyID based on a ManufacturerPartNumber form selection. This string
is then included in the recordset query.
The following error message only occurs when the ManufacturerPartNumber
string has many listings. When the string contains only 3 listings of
ManufacturerPartNumbers, this error message does not appear.
[Microsoft][ODBC Microsoft Access Driver] Expression too complex in query
expression 'LATITUDE <= 40.8210861895984 AND latitude >= 40.3865898104016 AND
longitude >= -74.2737786508004 AND longitude <= -73.7014913491996 AND
(((((((((((((((((((((((((((((ManufacturerPartNumber = '3408H')) OR
((ManufacturerPartNumber = '4608H'))) OR ((Manufacture'.
/customer_locator/testprocessform2.asp, line 233
Is there a size limit to a recordset query that is causing this error or is there a possible syntax issue with my query code?
Replace the code which generates the many, many nested parentheses with OR
logic with an IN caluse. A query resembling
.... 'LATITUDE <= 40.8210861895984 AND latitude >= 40.3865898104016 AND
longitude >= -74.2737786508004 AND longitude <= -73.7014913491996 AND
ManufacturerPartNumber IN('3408H', '4608H', ... )
should avoid this error.
Since we can't see your code I'm not sure just how to make the change.
John W. Vinson [MVP]
