Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

    Summing a calculated field of a query

    Good Day All,

    I have a list box whose data source is a query. In the query I have a calculated field "Bal". I want to sum this field. The code I am trying on the form is: sum(Bal) ; however this returns #Error.



    I would be grateful for some assistance with this.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If the form only shows one row from the query at a time, there's nothing to sum.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It will still sum all the records in the RecordSource.

    However, Sum expression in a textbox has nothing to do with query used as listbox RowSource. What is the form's RecordSource?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Thanks All,

    First, the listbox on the form shows several records.
    Second, I am assuming that once the listbox is on the form then the Rowsource of the list box implies the recordsource of the form. Is that correct?

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Sorry, I missed the fact that the query was for the listbox and not the form.
    First, the listbox on the form shows several records.
    Second, I am assuming that once the listbox is on the form then the Rowsource of the list box implies the recordsource of the form. Is that correct?
    No. You can look at the form recordsource in the properties pane to verify that.
    To get your sum in an unbound textbox, in the Form_Current event you could so aomething like:
    Code:
    txtBox = dsum("Bal","[SameQueryName as listbox]")
    Last edited by davegri; 09-16-2017 at 07:32 AM. Reason: clarif

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The expression could be directly in the textbox ControlSource property. Either way, it will sum ALL records in the table or query. If the form has additional filtering and you want to sum only the records on the form, that will require the DSum to have the same filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Please check this code for me; its getting an error message: Invalid use of Null

    Code:
    P
    
    rivate Sub Form_Current()
        Me.Text16 = DSum("Bal", Me.List2)
        Debug.Print Me.Text16
    End Sub

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    dSum has to be applied to a query or a table, not a form control. Review post #5.

    dDum("[this field]","[which is in this query or table]")
    Last edited by davegri; 09-17-2017 at 10:40 AM. Reason: clarif

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, a listbox RowSource and form RecordSource are not usually the same query, neither implies the other.

    If the form has that query as its RecordSource, why not use =Sum([Bal]) in textbox ControlSource? Even DSum() expression could be in ControlSource. In either case, VBA is not necessary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Here's the problem: The listbox has a query by the query builder; it is called "Customerlist". Hence, it does not appear as a query in the Query pane and thus cannot be referenced. So this is what I have and it still gives an error message.

    Code:
    Private Sub Form_Current()
       Me.Text16 = DSum("Bal", "[CustomerList]")
    End Sub

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Of course that errors - as already advised, domain aggregate function must reference table or query object.

    Why not use Sum() function in textbox ControlSource?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    OK, then. What is the listbox rowsource SQL that the query builder made for you? I think we can build a dSum, given that information.

  13. #13
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    I think this was advised against at comment #5

  14. #14
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    This is the SQL made by the query Builder. It is so long and daunting to use.

    Code:
    SELECT Invoices.[Invoice ID], Customers.Company, Invoices.[Invoice ID], [Invoices]![Original Total]-(Nz([CustomerPaymentsSum]![SumOfCustomer Payments])+Nz([Customer Deposits]![Customer Deposit],0)) AS Bal, Orders.[Order ID], Invoices.[Original Total], Orders.[Status ID]
    FROM (((Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]) INNER JOIN Invoices ON Orders.[Order ID] = Invoices.[Order ID]) LEFT JOIN CustomerPaymentsSum ON Invoices.[Invoice ID] = CustomerPaymentsSum.[Invoice ID]) LEFT JOIN [Customer Deposits] ON Orders.[Order ID] = [Customer Deposits].[Order ID]
    ORDER BY Customers.Company;

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    OK, now we have the query SQL. If we create a named query from that SQL, we can use that named query in a dSum function.
    Here's the VBA UDF to create a named query from a SQL string:
    sQueryName is the name you want to give to the named query, and sSQL is the query string to use.
    Code:
    Public Function fcnCustomizeSQL(sQryName As String, sSQL As String) As Boolean
        On Error GoTo fcnCustomizeSQL_Error
        Dim db   As DAO.Database
        Dim qdf  As DAO.QueryDef
        fcnCustomizeSQL = True
        Set db = CurrentDb
        On Error Resume Next
        With db
            .QueryDefs.Delete (sQryName)                'Delete the query if it exists
            On Error GoTo fcnCustomizeSQL_Error
            Set qdf = .CreateQueryDef(sQryName, sSQL)   'Create the query
        End With
        db.QueryDefs.Refresh
    fcnCustomizeSQL_EXIT:
        Set qdf = Nothing
        Set db = Nothing
        Exit Function
    fcnCustomizeSQL_Error:
        Select Case Err
            Case 3075
                fcnCustomizeSQL = False
            Case Else
                Msgbox Err.Number & ", " &  Err.Description, vbOKonly, " fcnCustomizeSQL"
                fcnCustomizeSQL = False
        End Select
        Resume fcnCustomizeSQL_EXIT
    End Function
    So in your form load event,
    Code:
    Dim strSQL as string
    strSQL = "SELECT Invoices.[Invoice ID], Customers.Company, Invoices.[Invoice ID], [Invoices]![Original Total]-(Nz([CustomerPaymentsSum]![SumOfCustomer Payments])+Nz([Customer Deposits]![Customer Deposit],0)) AS Bal, Orders.[Order ID], Invoices.[Original Total], Orders.[Status ID]FROM (((Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]) INNER JOIN Invoices ON Orders.[Order ID] = Invoices.[Order ID]) LEFT JOIN CustomerPaymentsSum ON Invoices.[Invoice ID] = CustomerPaymentsSum.[Invoice ID]) LEFT JOIN [Customer Deposits] ON Orders.[Order ID] = [Customer Deposits].[Order ID] ORDER BY Customers.Company;"
    Call fcnCustomizeSQL("MyQuery",strSQL)
    Now you have a named query in the Navigation pane. You can use it in your dSum:
    Code:
    txtBox16 = dSum("Bal","MyQuery")

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Summing calculated fields with DSum in a report
    By Carbontrader in forum Reports
    Replies: 3
    Last Post: 01-13-2017, 04:47 PM
  2. Summing calculated fields
    By George in forum Modules
    Replies: 3
    Last Post: 06-03-2016, 08:21 AM
  3. Replies: 1
    Last Post: 02-03-2016, 06:54 PM
  4. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  5. Summing Calculated Fields
    By Zoran in forum Queries
    Replies: 1
    Last Post: 03-31-2010, 01:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums