Very limited number of lines for criteria in query designer

[pschrader]

Hi !

I'm using MS Access 2003.

I have a problem with the number of lines that I can use to specify criteria
in the query designer.
For my database there are at present only 9 lines available,
but for other databases I have seen that many more lines can be available.
How can I increase this number ?

I could, of course, write a SQL statement instead of using the query
designer,
but I would prefer to enter a few key strokes to solve my problem.

Thank you very much for your kind help !

Peter

 

[Allen Browne]

Current versions of JET handle up to 100 ANDs or ORs in the WHERE clause,
but the query designer is more limited, as you say.

Personally, I don't find that to be a problem. You can use OR on a single
line in the criteria, e.g. by typing:
7 OR 8 OR 9

Where there are lots of values, you may find the IN operator better, e.g.:
IN (7, 8, 9)

The way JET interprets the rows of criteria from the query designer is:
(everything on one row) OR (everything on the next row) OR ...
That means if you want criteria of:
(Field1 = False) AND (Field2 = 'A' OR Field3 = 'B' OR Field4 = 'C')
you have to repeat the False on all 3 rows, and JET then writes this in the
most circumlocutory fashion:
((Field1 = False) AND (Field2 = 'A')) OR
((Field1 = False) AND (Field3 = 'B')) OR
((Field1 = False) AND (Field4 = 'C')) OR

In my experience, this is *way* too frustrating to handle with anywhere near
9 rows. The limit you refer to is therefore more of an visual thing than a
real issue. Switching to SQL View usually ends up with a much more efficient
result, and is easier to maintain.

 

[Rick Brandt]

Hi !

I'm using MS Access 2003.

I have a problem with the number of lines that I can use to specify
criteria in the query designer.
For my database there are at present only 9 lines available,
but for other databases I have seen that many more lines can be
available. How can I increase this number ?

I could, of course, write a SQL statement instead of using the query
designer,
but I would prefer to enter a few key strokes to solve my problem.

Thank you very much for your kind help !

Peter

With the cursor in a criteria row go to the menu bar...

Insert - Rows