Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Unanswered: Incorrect query result .... Access 2000

    Hi all.

    This one is driving me nuts!!!! Check the following code:

    SQLString = "SELECT Sum([Items Inventory].OnHand) AS Expr1 FROM [Items Inventory]"
    SQLString = SQLString & " WHERE ([Items Inventory].ItemID=" & ID & ");"

    Now running this query using ADO returns a result! THE TABLE THE QUERY IS BASED ON IS EMPTY! The result returned is the ID # provided. If I run this same exact query in the QBE only replacing the ID # with an actual number it returns a null set like it should ... Any ideas on what the **** is going on?

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    "SELECT Sum([Items Inventory].OnHand) AS Expr1
    FROM [Items Inventory]
    Group By ([Items Inventory].ItemID)
    WHERE ([Items Inventory].ItemID=" & ID & ");"

    You need a GROUP By when you have a Where clause

    S-

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    Sorry after reading your question I realized I misunderstood your question.

    Are you sure there is a value in ID variable when you code gets to the point of running the SQL statement?

    S-

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    S,

    Thanks for pointing that out ... I forgot that ... Geez, you'd think I'd just fell off the turnip truck today ... (It was yesterday). Thank God it's Friday ...

    Just saw your last post ... The GROUP BY fixed it ... Aggregate function ...

Posting Permissions

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