Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query will not work with 2003 data (97-SR2)

    My query runs fine when I use year 2002 data, ie between 12/1/2 And 12/31/2. However when I use between 1/1/3 And 1/31/3 it gives me the error message below. I have tried permutations of entering the date (01/01/03, 01/01/2003, etc.).

    This expression is typed incorrectly or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

    Here is my query SQL.

    SELECT Min(tbl_Dryer_Log.Date) AS MinOfDate, Max(tbl_Dryer_Log.Date) AS MaxOfDate, Count(tbl_Dryer_Log.App) AS CountOfApp, tbl_Dryer_Log.Intermediate, First(tbl_Dryer_Log.Product) AS FirstOfProduct, tbl_Dryer_Log.App, Sum(tbl_Dryer_Log.[Mix L]) AS [SumOfMix L], Sum(tbl_Dryer_Log.[Scrap L]) AS [SumOfScrap L], Sum(tbl_Dryer_Log.[Mix Kg]) AS [SumOfMix Kg], Sum(tbl_Dryer_Log.[Scrap Kg]) AS [SumOfScrap Kg], Sum(([tbl_Dryer_Log]![Mix L]+([tbl_Dryer_Log]![Mix Kg]/[tbl_Dryer_Log]![Mix SG]))-([tbl_Dryer_Log]![Scrap L]+([tbl_Dryer_Log]![Scrap Kg]/[tbl_Dryer_Log]![Mix SG]))) AS [Net L], tbl_Dryer_Log.HAP, Sum((([tbl_Dryer_Log]![Mix L]+([tbl_Dryer_Log]![Mix Kg]/[tbl_Dryer_Log]![Mix SG]))-([tbl_Dryer_Log]![Scrap L]+([tbl_Dryer_Log]![Scrap Kg]/[tbl_Dryer_Log]![Mix SG])))*[tbl_Dryer_Log]![Hc]*[tbl_Dryer_Log]![IF]) AS [HAP Lbs], tbl_Dryer_Log.IF, Sum(((([tbl_Dryer_Log]![Mix L]+([tbl_Dryer_Log]![Mix Kg]/[tbl_Dryer_Log]![Mix SG]))-([tbl_Dryer_Log]![Scrap L]+([tbl_Dryer_Log]![Scrap Kg]/[tbl_Dryer_Log]![Mix SG])))*[tbl_Dryer_Log]![Oc]*[tbl_Dryer_Log]![IF])/453.6) AS [VOC Lbs]
    FROM tbl_Dryer_Log
    WHERE (((tbl_Dryer_Log.Date)>=[Where start date is] And (tbl_Dryer_Log.Date)<=[Where end date is]))
    GROUP BY tbl_Dryer_Log.Intermediate, tbl_Dryer_Log.App, tbl_Dryer_Log.HAP, tbl_Dryer_Log.IF
    ORDER BY First(tbl_Dryer_Log.Product), tbl_Dryer_Log.App;

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query will not work with 2003 data (97-SR2)

    <P ID="edit" class=small>(Edited by patt on 16-Feb-03 09:49. Being more explicit about the solution)</P>Define the 2 parameters [Where start date is] And [Where end date is] on the PARAMETERS line at the start of the query, viz:

    PARAMETERS [Where start date is] Date, [Where end date is] Date;

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query will not work with 2003 data (97-SR2)

    I found the problem. I had a zero for a record. The value should always be greater than zero.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •