CODEWITHSUNDEEP
sqlms-access
user1087661
user1087661

Reputation: 101

MS Access Very Long and Getting "Expression Too Long" Error

I have an SQL with around 50 "OR" condition and all with "Like" operator. I execute this SQL from a VB Access code.

But the query fails saying, "Expression Too Long" error (mostly I think, because it exceeds 1024 characters in the query grid.

SELECT * 
  FROM <My Database> 
 WHERE (
        [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = '' 
        OR [Title] = ''
       ) 
       AND (
            [TITLE] LIKE '*Afghanistan*' 
            OR [TITLE] LIKE '*Term1*' 
            OR [TITLE] LIKE '*Term2*' 
            OR [TITLE] LIKE '*Term3*' 
            OR ..<around 40 like these> 
            OR [COUNTRY] LIKE '*Country1*' 
            OR ...<around 40 like these>
           );

Any ideas or suggestions as to how to work around this issue?

Upvotes: 0

Views: 1327

Answers (2)

Stephen Turner
Stephen Turner

Reputation: 7314

Firstly thank you for posting this as a seperate question to your previous one. That's the right way to do it :-)

The repeating OR [Title] = '' stuff should go, they are just clutter, you just need one of them, assuming you really need to match an empty title too.

Second, do you actually need the wildcards? If the database is designed well the field called Country should contain nothing but the name of the country, so you can just say [Country]='Afghanistan'.

If you don't need the wildcards then the IN keyword becomes useful: you can check for multiple options using [Country] IN ('Afghanistan', 'Term1', 'Term2', 'Term3')

onedaywhen's suggestion is very good (+1 from me), but I fear your code is pointing to some design problems. What are you trying to do with this code: Find everything? Validate input? Find based on user selections?

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

Suggestions: insert parameter values into base "staging" tables (possibly in another temp database) and create seni joins to these tables using EXISTS. Use ALIKE (rather than LIKE), which always uses Standard SQL wildcard characters (%).

SELECT * 
  FROM MyTable AS m
 WHERE EXISTS (
               SELECT * 
                 FROM MyExactParams AS x
                WHERE m.Title = x.Title
              )
       AND (
            EXISTS (
                    SELECT * 
                      FROM MyPatternParams AS p
                     WHERE m.Title ALIKE '%' + p.Title + '%'
                   )
            OR EXISTS (
                       SELECT * 
                         FROM MyPatternCountries AS c
                        WHERE m.Country ALIKE '%' + c.Country + '%'
                      )
           );

Upvotes: 4

Related Questions