Maximum number of Criteria Rows in Query Designer?

  • Thread starter Thread starter efandango
  • Start date Start date
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;
 
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.
 
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...
 
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...
 
Back
Top