Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    2

    Annoying Query issue

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

    This is currently the 'Ship List-MF(Machine)' 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 on the form seems to make no difference to the the records its selecting. Its simply selecting from the ChosenList.

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Posts
    99
    Try this in your WHERE clause:

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

  3. #3
    Join Date
    May 2006
    Posts
    2
    hi. ive tried this and when I use the form and fill-in the details for searching on I am getting an alert message saying..

    "This expression is typed incorrectly, or is too complex to be evaluated."
    ??

    any ideas?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Issue with conditional imput
    By Estyl in forum Forms
    Replies: 2
    Last Post: 02-19-2008, 10:42 AM
  2. SetFocus Issue
    By Sinjin in forum Access
    Replies: 0
    Last Post: 02-14-2008, 07:31 AM
  3. Access Issue
    By uk in forum Access
    Replies: 0
    Last Post: 07-04-2007, 09:08 AM
  4. access 2003 automation issue
    By BevA in forum Programming
    Replies: 2
    Last Post: 05-01-2006, 08:37 AM
  5. command button to filter a subform issue -
    By countdrako in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 11:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums