Can I have a Mandatory criteria in a query?

[efandango]

I have a query that has various 'OR' criteria relating to different fields,
which works fine. But regardless of which field criteria I choose I need to
have an additonal global 'mandatory criteria' that limits the records via a
form text box with this reference:

tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]



this is my current query:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points INNER JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode =
tbl_PostCodes.Postcode
GROUP BY tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Point_ID, tbl_points.Run_point_Venue,
tbl_points.Run_point_Address, Rnd([Point_ID])
HAVING (((tbl_points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))
OR
(((tbl_points.Run_Point_Postcode)=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]
And
(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]))
ORDER BY Rnd([Point_ID]);

 

[John W. Vinson]

I have a query that has various 'OR' criteria relating to different fields,
which works fine. But regardless of which field criteria I choose I need to
have an additonal global 'mandatory criteria' that limits the records via a
form text box with this reference:

tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]

Yes. This is easier in SQL view than in the query grid - you need to have your
parenthesis nesting correct. The mandatory criterion would need to be joined
via an AND criterion to the parenthesis nest containing the OR criteria.

UNTESTED AIR CODE, since of course I don't have your tables, with a lot of
Microsoft's uneeded extra parentheses removed, and reformatted for
readability; also, since you're not using any Counts, Sums or other Totals
query operations, I'd suggest unclicking the Totals Greek Sigma icon and
making it a simple select query with a WHERE clause rather than HAVING:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points INNER JOIN tbl_PostCodes
ON tbl_points.Run_Point_Postcode = tbl_PostCodes.Postcode
WHERE
tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]
AND
(
(tbl_points.Run_No Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To])
OR
(tbl_points.Run_Point_Postcode=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]
And
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])
ORDER BY Rnd([Point_ID]);


Try it, and count parentheses (adding +1 for each left and -1 for each right)
to see if you come out to 0. That's what I get but may have missed one!

 

[efandango]

John,

Simply superb!...

you more or less nailed it first time; like you said, you didn't have the
tables. (it's a shame there is no way of posting them on this great
newsgroup). I had to tweak it in the query designer, but got it to work
beautifully. Now I have a set of two pairs of combo boxes with: Run No,
Postcode, Area, Distrcit, Type. One of each combos go on Form A and Form B. I
can select any of each matching combo (eg: Area to Area) and limit them by
Run number: tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]

FYI, this is the initial code link that Karl Dewey helped me out with.

http://www.microsoft.com/office/com...n-us&mid=bee2bb11-3de8-43c6-9b7f-c5057da4f6bc

So, John (and Karl) thank you ever so much for helping me because this
functionality that I needed makes the whole database make sense and really
enpowers it.


This is the final SQL that did the trick:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points INNER 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_Postcode_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]);

kind regards

Eric

I have a query that has various 'OR' criteria relating to different fields,
which works fine. But regardless of which field criteria I choose I need to
have an additonal global 'mandatory criteria' that limits the records via a
form text box with this reference:

tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]

Yes. This is easier in SQL view than in the query grid - you need to have your
parenthesis nesting correct. The mandatory criterion would need to be joined
via an AND criterion to the parenthesis nest containing the OR criteria.

UNTESTED AIR CODE, since of course I don't have your tables, with a lot of
Microsoft's uneeded extra parentheses removed, and reformatted for
readability; also, since you're not using any Counts, Sums or other Totals
query operations, I'd suggest unclicking the Totals Greek Sigma icon and
making it a simple select query with a WHERE clause rather than HAVING:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode,
tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type,
tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points INNER JOIN tbl_PostCodes
ON tbl_points.Run_Point_Postcode = tbl_PostCodes.Postcode
WHERE
tbl_points.Run_No < [Forms]![frm_Runs].[txt_Run_Limit]
AND
(
(tbl_points.Run_No Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To])
OR
(tbl_points.Run_Point_Postcode=[Forms]![frm_Runs].[cbo_Point2Point_Postcode_From]
And
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])
ORDER BY Rnd([Point_ID]);


Try it, and count parentheses (adding +1 for each left and -1 for each right)
to see if you come out to 0. That's what I get but may have missed one!