E
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]);
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]);