Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    29

    Unanswered: Simple Sum problem for a form

    All this one is probably easy I just cannot figure it out.

    I have a text box in which I wish to display the total quantity of an item I that has not yet been received. This needs to check across multiple orders and and then check those orders for partial deliveries. Below probably explains it better than my word do. I assuming that it does not like the embedded SELECT statement.

    Me.txtOrder.Value = Nz(DSum("Quantity", "OrderingHistory", "ReceivedDate IS NULL AND LocalPN = " & Me.cboLocalPN.Value), 0) - Nz(DSum("Quantity", "Partials", "HistoryID = (SELECT ID FROM OrderingHistory WHERE RecievedDate IS NULL AND LocalPN = " & Me.cboLocalPN.Value & ")"), 0)

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think you can have SQL within a domain aggregate function. You should be able to replace that with a DLookup.
    Paul

  3. #3
    Join Date
    Jul 2007
    Posts
    29
    Dlookup will only return the first entry if there are multiple orders each with partial receipts I need to account for that. I am asking how to do that and get the result into a text box I can write it in SQL fairly easy. I just do not know how to funnel a number like that into a text box.

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    I would be tempted to create a form for Partial Receipts, and then use this form to interrogate each incomplete order. It is all very well, and it maybe sufficient to establish the value of the partial receipts, but what then?

    Simon

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Rather than have an embedded Select statement (SELECT ID FROM OrderingHistory WHERE RecievedDate IS NULL AND LocalPN = " & Me.cboLocalPN.Value), why not make this (SELECT ID FROM OrderingHistory WHERE RecievedDate IS NULL AND LocalPN = " & Forms!MyFormName!cboLocalPN & ") a saved query? or break it up and set another unbound text box to one of the values and use that value for processing into another text box.
    Last edited by pkstormy; 01-25-08 at 18:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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