Running into SQL stmt length limitation

Hi,
I have a very long SQL stmt and cannot be shortened. And I seem to be running into some sort of limitation on the length of the stmt. I'm search based on street names, i.e. zip = 12345 and (stn like 'main%' or stn like 'abc%'.....). The syntax is correct and when i add one more street, it gives me "syntax error".
Is there a way to go around this limitation?
Thank you,
Phil

I believe your problem is with the OR statements rather then the SQL length limitations. Search help for Microsoft access specifications and sleect the query topic. It talks about AND but I would assume

Below are 2 stmts. Now, I have it split up because of the "limitation". I am using MSA2000.

ZIP=94539 and (
stn like 'canyon Heights%' or
stn like 'kimber %' or
stn like 'kimberwood%' or
stn like 'estrella %' or
stn like 'benavente%' or
stn like 'platero%' or
stn like 'fisalia%' or
stn like 'hidalgo%' or
stn like 'orden%' or
stn like 'obispo%' or
stn like 'almeria%' or
stn like 'e las palmas%' or
stn like 'dolerita%' or
stn like 'valais%' or
stn like 'celada%' or
stn like 'delegado%' or
stn like 'madrid%' or
stn like 'imperio%' or
stn like 'loro %' or
stn like 'tordo%' or
stn like 'segovia%' or
stn like 'melendez%' or
stn like 'britto%' or
stn like 'mackintosh%' or
stn like 'slayton%' or
stn like 'palatino%' or
stn like 'recino%' or
stn like 'esparito%' or
stn like 'gaucho%' or
stn like 'abuelo%' or
stn like 'clara %' or
stn like 'lucy %' or
stn like 'palacio%' or
stn like 'castro %' or

stn like 'las palmas%' or
stn like 'san moreno%' or
stn like 'san simeon%' or
stn like 'catalina%' or
stn like 'lucinda%' or
stn like 'san sebastian%' or
stn like 'san carlos%' or
stn like 'hacienda%' or
stn like 'seville%' or
stn like 'andorra%' or
stn like 'valencia%' or
stn like 'la jolla%' or
stn like 'carmelita%' or
stn like 'el dorado%' or
stn like 'dalgo%' or
stn like 'caliente%' or
stn like 'helado%' or
stn like 'encanto%' or
stn like 'ondina%' or
stn like 'jacaranda%' or
stn like 'ferreira%' or
stn like 'amapola%' or
stn like 'mission villa%' or
stn like 'los pinos%' or
stn like 'optimo%' or
stn like 'tirso%' or
stn like 'rainwater%' or
stn like 'lemos%' or
stn like 'chiltern%' or
stn like 'laguna%' or
stn like 'ambar %' or
stn like 'gualala%' or
stn like 'capa %' or
stn like 'marino%' or
stn like 'laredo%' or
stn like 'casa marcia%' or
stn like 'quintana%' or
stn like 'la purissima%' or
stn like 'valdez%' or
stn like 'dolores%' or
stn like 'bermuda%' or
stn like 'bjada%' or
stn like 'debora%' or
stn like 'camero %' or
stn like 'canto%' or
stn like 'durillo%' or
stn like 'cascado%' or
stn like 'cantere%' or
stn like 'gomes%' or
stn like 'pajaro%' or
stn like 'marabu%' or
stn like 'calido%' or
stn like 'mino %' or
stn like 'una %' or
stn like 'vaca %' or
stn like 'oro %')

---------
GOMES ELEMENTARY - BY STREET
Syntax too long if combined above
south of Paseo Padre

ZIP=94539 and (
stn like 'driscoll%' or
stn like 'shadow brooke%' or
stn like 'terry%' or
stn like 'marty%' or
stn like 'nunes%' or
stn like 'cruz%' or
stn like 'rioja%' or
stn like 'valero%' or
stn like 'sueno%' or
stn like 'abaca%' or
stn like 'corriea%' or
stn like 'bairo%' or
stn like 'davila%' or
stn like 'joyce%' or
stn like 'kalenda%' or
stn like 'cornac%' or
stn like 'ramon %' or
stn like 'briscoe%')

I believe your problem is with the OR statements rather then the SQL length limitations. Search help for Microsoft access specifications and sleect the query topic. It talks about AND but I would assume this applies to OR as well.

Microsoft Access database query specifications
Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255

Number of ANDs in a WHERE or HAVING clause 40

Number of characters in an SQL statement approximately 64,000

So you will simply have to recude the OR statements per query or use another dbms I think.

SELECT tblClient.*
FROM tblClient
WHERE (((tblClient.Streetname)=(SELECT tblStreet.StreetName FROM tblStreet WHERE (((tblStreet.StreetName) Like [tblClient].[Streetname] & "*"))))) AND ((tblClient.Zipcode)=90210));

Thank you all.
jobrienct identified the real problem so he got the most points.
Other solutions were good but unfortunately, in my case, it's not workable but it does give me some new ideas.
thanks again.

Glad we could help. I'm only sorry it doesnt resolve your issues. Frankly I think ornicar's original suggestion is on the money even if it requires redesigning the tables. There is no end to the number of streets that could be added and as such that data needs to reside in a table rather then as constants in a query IMO.

that solution requires me to have more tables (or flags within tables) for different searches. the search string above is only one of many i do. that's why that solution is not ideal. i may be able to use that idea and section off different portions of the city and code them into the table. i may do it if i Have to. thanks again.

0

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…