Query issues

[StevieD via AccessMonster.com]

Hi im struggling with a query im trying to run. Unfortunately I know very
little about Access and have been lumbered with the responsibility of sorting
out the following issue, so any assistance (dumbed down) would be much
appreciated...

We're currently running the following Query...

PARAMETERS [Forms]![emplist]![EnterStartDate] DateTime, [Forms]![emplist]!
[EnterFinishDate] DateTime;
SELECT DISTINCTROW [SALES STATUS].Ss_WO_NO, [SALES STATUS].[Ss_DATE_REC'D],
[DESPATCH STATUS].Ds_Instance, [SALES STATUS].[Ss_DATE_REC'D], [SALES STATUS].
Ss_QUOTE_NO, [SALES STATUS].Ss_CUSTOMER_NAME, [SALES STATUS].Ss_CODE, [SALES
STATUS].Ss_CUST_ORD_NO, [SALES STATUS].[Ss_PART_NO/DRWG], [SALES STATUS].
Ss_ISSUE_NO, [SALES STATUS].Ss_QTY, [SALES STATUS].Ss_PRICE_PER_100,
[DESPATCH STATUS].[Ds_DATE_Ship_Req'd], [DESPATCH STATUS].Ds_WK_ACK,
[DESPATCH STATUS].[Ds_QTY_REQ'D], [DESPATCH STATUS].[Ds_DATE_REQ'D],
[DESPATCH STATUS].[Ds_WK_REQ'D], [DESPATCH STATUS].Ds_DESPATCH_QTY, [DESPATCH
STATUS].Ds_DESPATCH_DATE, [Ds_despatch_qty]*[Ss_PRICE_PER_100]/100 AS Expr1,
[DESPATCH STATUS].Ds_DATE_ACK, [DESPATCH STATUS].Ds_Ship_Comment, MachineCode.
MachineCode AS MCA, MachineCode_1.MachineCode AS MCB, MachineCode_2.
MachineCode AS MCC, Status.Status AS SA, Status_1.Status AS SB, Status_2.
Status AS SC, MachineCode_1.MachineCode, MachineCode_2.MachineCode,
MachineCode.MachineCode
FROM [SALES STATUS] INNER JOIN (((((([DESPATCH STATUS] LEFT JOIN MachineCode
ON [DESPATCH STATUS].MachineCodeA = MachineCode.ID) LEFT JOIN MachineCode AS
MachineCode_1 ON [DESPATCH STATUS].MachineCodeB = MachineCode_1.ID) LEFT JOIN
MachineCode AS MachineCode_2 ON [DESPATCH STATUS].MachineCodeC =
MachineCode_2.ID) LEFT JOIN Status ON [DESPATCH STATUS].StatusA = Status.ID)
LEFT JOIN Status AS Status_1 ON [DESPATCH STATUS].StatusB = Status_1.ID) LEFT
JOIN Status AS Status_2 ON [DESPATCH STATUS].StatusC = Status_2.ID) ON [SALES
STATUS].Ss_WO_NO = [DESPATCH STATUS].Ds_WO_NO
WHERE ((([DESPATCH STATUS].[Ds_DATE_Ship_Req'd]) Between [Forms]![emplist]!
[EnterStartDate] And [Forms]![emplist]![EnterFinishDate]) AND ((MachineCode_1.
MachineCode)=[Forms]![emplist]![ChosenList])) OR (((MachineCode_2.MachineCode)
=[Forms]![emplist]![ChosenList])) OR (((MachineCode.MachineCode)=[Forms]!
[emplist]![ChosenList]));

It seems to work to an extent. However, entering the StartDate and FinishDate
seems to make no difference to the the records its selecting. Its simply
selecting from the ChosenList.

 

[John Vinson]

It seems to work to an extent. However, entering the StartDate and FinishDate
seems to make no difference to the the records its selecting. Its simply
selecting from the ChosenList.

When you use OR in a query, you have to be careful to nest the
parentheses correctly. In this case you have (parentheses parsed out
for readability)
:
WHERE
(
(
(
[DESPATCH STATUS].[Ds_DATE_Ship_Req'd]
)
Between [Forms]![emplist]![EnterStartDate]
And [Forms]![emplist]![EnterFinishDate]
)
AND
(
(MachineCode_1.MachineCode)=[Forms]![emplist]![ChosenList]
)
)
OR
(
(
(
MachineCode_2.MachineCode
)
=[Forms]![emplist]![ChosenList]
)
)
OR
(
(
(
MachineCode.MachineCode
)
=[Forms]![emplist]![ChosenList]
)
)
;

Note that you have the date range in the first major clause. If EITHER
of the other two clauses - after the first OR - is TRUE, then the
record will be retrieved, regardless of the truth or falsity of the
date range! That's what OR does: retrieves the record if *any one* of
the OR'd conditions is true.

Try rearranging the WHERE clause to:

WHERE
[DESPATCH STATUS].[Ds_DATE_Ship_Req'd]
Between [Forms]![emplist]![EnterStartDate] And
[Forms]![emplist]![EnterFinishDate]
AND (MachineCode_1.MachineCode=[Forms]![emplist]![ChosenList]
OR MachineCode_2.MachineCode=[Forms]![emplist]![ChosenList]
OR MachineCode.MachineCode=[Forms]![emplist]![ChosenList]);

This nests all three MachineCode criteria in parentheses so that it
checks all three; it then combines this using AND with the date range
criterion.

John W. Vinson[MVP]