Criteria limit in queries or SQL

[geralf]

Hi,

The limit of number of criterias in a SQL is 40. Is there some workaround this limit or is there some other way to do it.

I use a multiselect listbox to select criterias, and some users may select more than 40 criterias from the listbox.

Thanks in advance.

 

[raskew]

Gerhard-

Is it possible to post an example (Acc97, please) of what you're up against?

Best wishes - Bob

 

[SQL_Hell]

Hi,

No way! 40 criteria, I gotta see this,

there must be a better way you can design this solution

post an example database, any format for me is ok.

Cheers

 

[geralf]

Hi guys!

It will take a little time to make a sample, because I'd have yo strip out the non-interessting parts, and convert to english so it would get readable. For now I've just built a query using my search form 's lmultiselect listbox. This listbox has 700 records or items that can be selected, and grows with over 100 items each month. This listbox lists processes run in a production factory. So the user can select whatever processes he/she wants presented in a report by clicking in the list. Therfore the selected count can easily reach 40. Of course, I could tell the user that after selection no. 35 that 'you have 5 left' and so on.

The items can be selected without any 'common relationship or things' which could group them. Therfore my question.

Here's an example of one of the form-built SQL's:

SELECT tblProduksjonsjournal.PKM, tblProduksjonsjournal.CID, CDate(Format([PDATO],'dd/mm/yyyy')) AS D, G002A.Sows, [SVEKT]+[IVEKT] AS M, Utbytte([G002].[CID]) AS Yield FROM (G002 INNER JOIN tblProduksjonsjournal ON G002.CID = tblProduksjonsjournal.CID) INNER JOIN G002A ON G002.CID = G002A.CID WHERE tblProduksjonsjournal.CID=95423 OR tblProduksjonsjournal.CID=95323 OR tblProduksjonsjournal.CID=95123 OR tblProduksjonsjournal.CID=94923 OR tblProduksjonsjournal.CID=94723 OR tblProduksjonsjournal.CID=94523 OR tblProduksjonsjournal.CID=94323 OR tblProduksjonsjournal.CID=94123 OR tblProduksjonsjournal.CID=93923 OR tblProduksjonsjournal.CID=93723 OR tblProduksjonsjournal.CID=93523 ORDER BY tblProduksjonsjournal.PKM;

I have not taken the time to 'format' this SQL the 'normal' way since it's built automatically on the selections from the user.

Thanks for your replies and interest.

 

[raskew]

Gerhard-

If you to rewrite the code that builds the query so that it adds each choice to a string, think you might be able to do something like this:

SELECT
    tblProduksjonsjournal.PKM
  , tblProduksjonsjournal.CID
  , CDate(Format([PDATO],'dd/mm/yyyy')) AS D
  , G002A.Sows
  , [SVEKT]+[IVEKT] AS M
  , Utbytte([G002].[CID]) AS Yield 
FROM
   (G002 
INNER JOIN
   tblProduksjonsjournal 
ON
   G002.CID = tblProduksjonsjournal.CID) 
INNER JOIN
   G002A 
ON
   G002.CID = G002A.CID 
WHERE
   InStr("95423, 95323, 95123, 94923, 94723, 94523, 94323, 94123, 93923, 93723, 93523", tblProduksjonsjournal.CID) > 0
ORDER BY
   tblProduksjonsjournal.PKM;

Try plugging that it in, then post back with the problems.

Best wishes-

Bob

 

[Pat Hartman]

You could use the In() clause rather than InStr().

WHERE
    tblProduksjonsjournal.CID In(95423, 95323, 95123, 94923, 94723, 94523, 94323, 94123, 93923, 93723, 93523)
ORDER BY
   tblProduksjonsjournal.PKM;

BTW, the ampersand (&) is the proper concatenation character for Access rather than the plus (+). The plus is an arithmetic operator and although it in some cases works, you can also get unexpected results. Check out help for details.

 

[geralf]

Pat,

BTW, the ampersand (&) is the proper concatenation character for Access rather than the plus (+). The plus is an arithmetic operator and although it in some cases works, you can also get unexpected results. Check out help for details.

I guess you're refering to this:

SELECT
tblProduksjonsjournal.PKM
, tblProduksjonsjournal.CID
, CDate(Format([PDATO],'dd/mm/yyyy')) AS D
, G002A.Sows
, [SVEKT]+[IVEKT] AS M

In my query. I know I I shal use the ampersand to connect strings, but in this case the [SVEKT]+[IVEKT] are numeric fields that I have to add toghether, arithmetically speaking. Am I missing something here?

I'll try out the suggested InStr function and In Clause and let you know. If all works out, I'll dare switch the listbox multiselect property from 'simple' to 'extended'.

Thank you very much for your help. Have a nice day!

 

[KKilfoil]

Alternative approach....

You could create a new table to contain all of the users' choices, and then join these choices against your data table in a query.

You could even make this table store 'old reports criteria ' by adding some sort of 'batch number', so that the user need not reselect all 40+ choices each time they want to run a report.

Obviously, the 40 limit disappears completely with this approach.

 

[Pat Hartman]

Not sure why I assumed you were concatenating two fields rather than adding them.

 

[raskew]

Pat -

A 'senior-moment' perhaps.

Best wishes - Bob

 

[geralf]

Sorry for the late feedback here, but here it is.

Bob, I tried Pat's solution first, since I thought that was the one with less modifications. It worked very nice. Now I guess it's the SQL's string-length which is the limit. I tested it on a hundred 'criterias' or so and it worked very well.

I got some errors when I tried your's Bob, and played around with the double and single quotes without getting it to work. I see what you where thinking, but it won't play ball.

KKilfoil, this was also a great idea I hadn't thoght of. For now I'm using Pat's solution until I develop the next version. Then I might use your approach, and incorporate some kind of 'search-profiles'

Thanks for the help all. Very much appreciated.