Results 1 to 7 of 7

Thread: Dsum #error

  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Dsum #error

    I am stumped over DSUM. I have a datasheet subform consisting of a few fields PurchasedQty, ConsumedQty and txtRemain. The subform is bound to tblsubProduct. txtRemain is an unbound field and its control source is

    =DSum("[PurchasedQty]-[ ConsumedQty]","tblsubProduct","(QtyID<= " & NZ([txtQtyID],0) & ") And ([ItemDesc]= '" & Replace([ItemDesc],"'","''") & "')")

    The data type is as follows
    PurchasedQty number (default 0)
    ConsumedQty number (a combo default 0)
    QtyID autonumber
    ItemDesc text

    The result is fine except for new record at the bottom of the datasheet subform where the record selector points when the form opens. When IDate and ConsumedQty values are entered, txtRemain calculates and reads correctly but new record again shows #error as the record selector moves to a new record

    IDate PurchasedQty ConsumedQty txtRemain
    11/02/2008 ...........30 0 30
    15/02/2008............ 0 2 28
    18/02/2008 ........... 6 2 32
    ........................... 0 0 #Error

    Can any expert please help? Thank you in advance
    Last edited by big_mon; 06-20-08 at 04:35.

  2. #2
    Join Date
    Jan 2008
    Posts
    54
    Dsum #error

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Looks like there is nothing wrong with your DSUM.

    All you need to do is something like:

    IIf(IsNull(IDate),<your DSum>,0)

    In other words, test for the new record / missing data before executing your DSUM expression.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2008
    Posts
    54
    StarTrekker, you are absolutely right. Testing for null date solve my problem.
    Great! Thank you for expert advice

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jul 2012
    Posts
    2

    DSUM error

    Hey I am using same query above but result is not showing in report it give error undefined 'dsum' expression.

    If it will go ahead so i will use iff command. Kindly help me.

  7. #7
    Join Date
    Jul 2012
    Posts
    2
    My query is below kindly help me.
    Thanks in advance.

    SELECT Stock_Card.StockCardID AS StockCardIDAlias, Stock_Card.StockID AS StockIDAlias, Stocks.Stock, Stock_Card.DateInsert, Stock_Card.ID, Stock_Card.Type, Stock_Card.RefNo1, Stock_Card.Pieces1, Stock_Card.Cost, [Stock_Card]![Cost]*[Pieces1] AS PurchaseAmount, Vendors.Company AS vendor, Stock_Card.RefNo2, Stock_Card.Pieces2, Stock_Card.SalesPrice, [Stock_Card]![SalesPrice]*[Pieces2] AS SalesAmount, Clients.Company AS client, Stock_Card.ICode,
    DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <" & [StockCardIDAlias] & " And [StockID] = " & [StockIDAlias] & "") AS Previous, DSum("[Pieces1]-[Pieces2]","Stock_Card","[StockCardID] <=" & [StockCardIDAlias] & " And [StockID] = " & [StockIDAlias] & "") AS [On Hand]
    FROM Clients RIGHT JOIN (Vendors RIGHT JOIN (Stock_Card LEFT JOIN Stocks ON Stock_Card.StockID = Stocks.StockID) ON Vendors.VendorId = Stock_Card.VendorID) ON Clients.ClientID = Stock_Card.ClientID;

Posting Permissions

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