Maximum number of Criteria Rows in Query Designer?

E

efandango

I seem to have used up the maxium number of rows for the Criteria/'OR'
option. I have use a total of 9 rows. 1 for the first criteria and a further
8 for the OR options.

I need to add two more rows of 'OR' criteria for a given field column. Does
anyone have any thoughts or idea of how I can do this?

Each field 'OR' criteria column clause points to a combobox on a form, and I
need to reference one more field with two criterai OR options. The field in
question is [Getround_Flag] and I need to add the following two criteria to
it:

[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_From]
[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_To]


my current SQL is:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue,
tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode =
tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
(tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_From])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_To])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_To]))
ORDER BY Rnd([Point_ID]) DESC;
 
F

fredg

I seem to have used up the maxium number of rows for the Criteria/'OR'
option. I have use a total of 9 rows. 1 for the first criteria and a further
8 for the OR options.

I need to add two more rows of 'OR' criteria for a given field column. Does
anyone have any thoughts or idea of how I can do this?

Each field 'OR' criteria column clause points to a combobox on a form, and I
need to reference one more field with two criterai OR options. The field in
question is [Getround_Flag] and I need to add the following two criteria to
it:

[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_From]
[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_To]

my current SQL is:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue,
tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode =
tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
(tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_From])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_To])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_To]))
ORDER BY Rnd([Point_ID]) DESC;
Click to expand...

You're not limited to the number of rows you can see in the default
QBE view..
Select one or more rows (at the left edge) and simply click on Insert
+ Rows
Or just open the query design in SQL View and manually write the
additional AND/OR clause in.
Note that there is a maximum of 99 ANDS in a Where or Having clause.
 
A

Allen Browne

Although the graphic query designer has a limited number of rows, JET can
handle much more than that if you edit the query statement directly.

The WHERE clause would end up something like this:
((tbl_points.Run_No Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To])
AND (tbl_points.Run_No) <
[Forms]![frm_Runs].[txt_Run_Limit]))
OR ((tbl_points.Run_No <
[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode =
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From])
OR (tbl_points.Run_Point_Postcode =
[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To])
OR (tbl_PostCodes.Area =
[Forms]![frm_Runs].[cbo_Point2Point_Area_From])
OR (tbl_PostCodes.Area =
[Forms]![frm_Runs].[cbo_Point2Point_Area_To])
OR (tbl_PostCodes.District =
[Forms]![frm_Runs].[cbo_Point2Point_District_From])
OR (tbl_PostCodes.District =
[Forms]![frm_Runs].[cbo_Point2Point_District_To])
OR (tbl_points.Point_Type =
[Forms]![frm_Runs].[cbo_Point2Point_PointType_From])
OR (tbl_points.Point_Type =
[Forms]![frm_Runs].[cbo_Point2Point_PointType_To]))

Watch the brackets, and you can add some more ORs in the right place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
I seem to have used up the maxium number of rows for the Criteria/'OR'
option. I have use a total of 9 rows. 1 for the first criteria and a
further
8 for the OR options.

I need to add two more rows of 'OR' criteria for a given field column.
Does
anyone have any thoughts or idea of how I can do this?

Each field 'OR' criteria column clause points to a combobox on a form, and
I
need to reference one more field with two criterai OR options. The field
in
question is [Getround_Flag] and I need to add the following two criteria
to
it:

[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_From]
[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_To]


my current SQL is:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue,
tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode =
tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
(tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_From])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_To])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_To]))
ORDER BY Rnd([Point_ID]) DESC;
Click to expand...
 
E

efandango

Doh!... i feel really stupid in not seeing that.

Thanks Fred (and Allan) for responding.

regards

Eric



fredg said:
I seem to have used up the maxium number of rows for the Criteria/'OR'
option. I have use a total of 9 rows. 1 for the first criteria and a further
8 for the OR options.

I need to add two more rows of 'OR' criteria for a given field column. Does
anyone have any thoughts or idea of how I can do this?

Each field 'OR' criteria column clause points to a combobox on a form, and I
need to reference one more field with two criterai OR options. The field in
question is [Getround_Flag] and I need to add the following two criteria to
it:

[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_From]
[Forms]![frm_Runs].[cbo_Point2Point_Getrounds_To]

my current SQL is:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue,
tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode =
tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
(tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_From])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.Area)=[Forms]![frm_Runs].[cbo_Point2Point_Area_To])) OR
(((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_PostCodes.District)=[Forms]![frm_Runs].[cbo_Point2Point_District_To]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_From]))
OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND
((tbl_points.Point_Type)=[Forms]![frm_Runs].[cbo_Point2Point_PointType_To]))
ORDER BY Rnd([Point_ID]) DESC;
Click to expand...

You're not limited to the number of rows you can see in the default
QBE view..
Select one or more rows (at the left edge) and simply click on Insert
+ Rows
Or just open the query design in SQL View and manually write the
additional AND/OR clause in.
Note that there is a maximum of 99 ANDS in a Where or Having clause.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I have a Mandatory criteria in a query? 2
Criteria linked to form combo 'Yes/No or All' expression help 12
criteria 'between' explicit value 1 and Value 2 only. 2
criteria 'between' value 1 and Value 2 only 2
"Query is too complex" - limit to number of parameters in query? 3
Query: Two Dates in one cell of a field. Need criteria help 2
Is there a maximum number of criteria that Excel query can handle 0
Report with set number of rows per page 7

Top