Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Gives Wrong Answer

    In Access 97, SR-2, in a table of intermediate results I am re-calculating fields over a range of data. One of the update queries gives the wrong answer on the last record if the record is the last record in the table. The query is:

    UPDATE tblDetails SET tblDetails.curUnitCost = ([curTotalCost]/[curQuantity])
    WHERE (((tblDetails.txtWorkOrder)=[Work Order]));

    If I add a record with almost no data (just the one key field), the results are all o.k. If I go back and re-calculate any other set of data, the results are all o.k. The only result that is ever wrong is the last one for that field in the last record. The incorrect result is always a large negative number when it should never be negative. The fields are defined as Currency type.

    Any suggestions?

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Gives Wrong Answer

    I don't see anything wrong with the query, but it isn't clear where the fields curTotalCost,curQuantity and WorkOrder are coming from, so it's nearly impossible to answer your question. If they're variables, then you need to make sure that they're being properly populated. If they're fields on a form, ditto. The phrase "WHERE (((tblDetails.txtWorkOrder)=[Work Order]))" suggests that they come from somewhere other than tblDetails, so you might try giving us a little more information to work with.
    Charlotte

  3. #3
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Gives Wrong Answer

    All of the fields appear in the table tblDetails. Work Order is the selection parameter in the query, curQuantity is a value that is user input, curTotalCost is a value that is computed by a previous query. In all cases they all contain the appropriate values in all records in the table. The only problem is this one value that is the last row in this column in the table.

    If I add new rows of data, the last value is mis-calculated this way and the value that was in this position before the new data was added is re-calculated correctly.

    Paul

  4. #4
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Gives Wrong Answer

    Charlotte, the query is o.k. By writing a VBA routine to do the work of the query and stepping through it, I found that another VBA routine was wrong. Under certain conditions, this other routine was supposed to modify a value in this column and it always picked on this last value. Sometimes it was wrong.

    Sorry for the trouble. I appreciate your help and apologize for yelling too soon.

    Paul

Posting Permissions

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