Limits Sql

[Vulcan1500]

I have a form with buttons and a list box. Pressing a number of buttons a SQL-string with a WHERE-string (based on the buttons pressed) is build and executed after pressing the search button. The the recordset complying the requested criteria (buttons) is displayed in the list box. After increasing the number of pressed buttons suddenly no record is listed in the box. I'm expecting that I'm exceeding the limits of Access, but I'm not sure due to what I found on the internet on this issue. My question is now if anybody recognises this problem and can help me to solve this? Is the length of the SQL-string including WHERE-string limited, or the number of AND's or OR's in the criteria?

 

[twoplustwo]

You mean you set the criteria of a query by clicking a number of buttons right?

I'd probably write the SQL string with the "WHERE" clause in a series of IF statements. Poast back if this sounds reasonable.

Edit: In VBA!

 

[neileg]

The correct funtioning of AND and OR in the WHERE clause can require careful positioning of the brackets. I'm wondering if the variable construction of the WHERE is getting these brackets right?

 

[Vulcan1500]

Indeed, criteria are set by clicked buttons and the WHERE-part starts with the if statements. This works fine and fast so the bracketing is correct.

The problem is that when I increase the number of clicked buttons the number of found records increases or decreases as expected, but suddenly (after clicking another extra button) no records are found, which is not expected. Unclicking the last button it gives again the previous set. Although Access is not reporting any error I get the feeling that I'm exeeding its limits.

For your information I have approx 50 buttons and some are based on AND and some on OR. SQL-string length goes easy up to 10,000 characters and more and number of and's/or's in string up to 100 and more.

 

[neileg]

Now found this from Access Help for A2003

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 99
Number of characters in an SQL statement approximately 64,000

So if you do have more than 99 AND/OR then you're exceeding the limit, at least for this version of Access.

 

[twoplustwo]

50? Really?

What kind of stuff is this for if you don't mind me asking. It seems excessive

 

[Vulcan1500]

It is a database with projects executed world wide. The produced list is used for prequalifications. Projects can be searched within a timeperiode, turnover, type, area (14 piece) and activity. During the input process of a project three different activities (main and 2 sub) out of 50 can be allocated to the project.

I also found these limitations on the net, but am exeeding these easily before the search does not give any record anymore. I'm using Access 2003.

 

[twoplustwo]

If you absolutely need all these the only way I can figure is to have a set of WHERE clauses nested in IF statements after the main SQL (lol @ writing 200+ though).

Maybe some form of cascading combo box design might be better than having a big ass text string? Just ideas at the min.

I dunno, interesting problem.

 

[neileg]

Maybe some form of cascading combo box design might be better than having a big ass text string? Just ideas at the min.

That's what I was thinking.