Struggling with Query.

[StevieD via AccessMonster.com]

Im having a nightmare. Unfortunately I know pretty much nothing 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
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]));

This works fine. It all ties into the report setup for it, etc...

However, what basically happens is that the Form 'emplist' has two combo
boxes and a StartDate and EndDate field. The date fields work fine and are
what are currently tied into the above query. The combo boxes are setup to
switch values from each box back and forth. (ChooseList and ChosenList).

I need to be able to run the query so that it can pick all relevant records
with the correct dates but to also include one or more of the values in
ChosenList in the search, and for it to pick from the 3 MachineCode fields in
the Despatch Status table.. (MachineCodeA, MachineCodeB, MachineCodeC)...

Im sure none of that probably makes any sense at all. If anyone thinks they
may be able to help I could email you the database we're trying to work on to
maybe help give you a better idea of what we're trying to achieve.

Thanking in advance..

 

[Guest]

drwilsonzoo at hotmail dot com

"MSDN HELP and Name"

I think I know what you are trying to do and it should be a simple fix. If I
can comprehend the entire thing. First I need some background though.

1. correct dates (you input into the query)?
2. one or more of the values in ChosenList (this could be the tricky part)
3. 3 MachineCode fields in the Despatch Status table (shold not be that bad)

Is the query SQL or VB or did you just type it in to show the fields?

Im having a nightmare. Unfortunately I know pretty much nothing 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
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]));

This works fine. It all ties into the report setup for it, etc...

However, what basically happens is that the Form 'emplist' has two combo
boxes and a StartDate and EndDate field. The date fields work fine and are
what are currently tied into the above query. The combo boxes are setup to
switch values from each box back and forth. (ChooseList and ChosenList).

I need to be able to run the query so that it can pick all relevant records
with the correct dates but to also include one or more of the values in
ChosenList in the search, and for it to pick from the 3 MachineCode fields in
the Despatch Status table.. (MachineCodeA, MachineCodeB, MachineCodeC)...

Im sure none of that probably makes any sense at all. If anyone thinks they
may be able to help I could email you the database we're trying to work on to
maybe help give you a better idea of what we're trying to achieve.

Thanking in advance..