none
getting sum for continuous field RRS feed

  • Question

  • Hello.  The Access 2013 front end is using sql server 2008 r2 linked tables and views.  I have a continuous form with 7 fields for each row to use as a display for budget items.  The first 3 fields come from a budget table.  The 4th field comes from a view.  The remaining fields are calculated fields, such as percent, difference, etc.

    My difficulty is coming in the 4th field which comes from the view.  I have been trying to use DSum but that wasn't working.  I went to use a sql select sum statement and I got the NAME? in the field.  The code for both tries is posted below.  One of the criteria of the 4th field is the accounting number in field 1 (me.txtItemNumber.value).  This is a text since some have a letter in it.  Sample accounting numbers are 5010, 5040-A, 6030, and so on, which is why the field is text.  

    In the code below it is the criteria in the dsum >>> AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' where is seems to fail the most.  If I take the sql statement to the query design of Access and paste it and change YearID = 1 and the above line to use '6030' and run it, it returns back the correct number.  

    The code is behind a button.  It works until the DSum or below it is reached.  I am in a quandry over this. So any assistance given is appreciated.  Or if there is a better way of doing this, I'm open to suggestions.

    ...John

        'filling the budget information view only form
        Dim sqlBudget As String
        
        Me.RecordSource = ""
        
        sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount " & _
                        "FROM dbo_vwBudget " & _
                        "WHERE IsRemove = 0 " & _
                            "AND YearID = " & Me.cboYearID.Value & " " & _
                        "ORDER BY LineItemAccountingNumber  ASC"
        
        Me.RecordSource = sqlBudget
        
        Me.txtItemNumber.ControlSource = "LineItemAccountingNumber"
        Me.txtLineItem.ControlSource = "BudgetCategoryLineItem"
        Me.txtBudgetAmount.ControlSource = "BudgetAmount"
    
    '    Me.txtAmountUsed.ControlSource = DSum("TransactionAmount", "dbo_vwTransaction", "[Remove] = 0 AND [YearID] = " & Me.cboYearID.Value & " AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' ")
    
    
        Dim sqlUsed As String
    
        sqlUsed = "SELECT Sum(TransactionAmount) " & _
                    "FROM dbo_vwTransaction " & _
                    "WHERE Remove = 0 " & _
                        "AND YearID = " & Me.cboYearID.Value & " " & _
                        "AND LineItemAccountingNumber = '" & Me.txtItemNumber.Value & "' "
    
        Me.txtAmountUsed.ControlSource = sqlUsed
    

    Saturday, November 21, 2015 6:48 AM

Answers

  •     
        sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount " & _
                        "FROM dbo_vwBudget " & _
                        "WHERE IsRemove = 0 " & _
                            "AND YearID = " & Me.cboYearID.Value & " " & _
                        "ORDER BY LineItemAccountingNumber  ASC"
        
    ...
    
    '    Me.txtAmountUsed.ControlSource = DSum("TransactionAmount", "dbo_vwTransaction", "[Remove] = 0 AND [YearID] = " & Me.cboYearID.Value & " AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' ")
    
    
    

    Hi John,

    Two remarks:

    - You assign the result of DSum to a ControlSource. I think you only must use  Me.txtAmountUsed = DSum(...)

    - In constructing your sql_string, then I find it easier to see if you did not omit a separating space, if you place them in front of the lines:

    sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount _
              & " FROM dbo_vwBudget _
              & " WHERE IsRemove = 0" _
              & " AND YearID = " & Me.cboYearID.Value _
              & " ORDER BY LineItemAccountingNumber  ASC"
    

    Imb.

    • Proposed as answer by David_JunFeng Tuesday, November 24, 2015 1:16 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 9:05 AM
    Saturday, November 21, 2015 8:43 AM
  • Hello.  The Access 2013 front end is using sql server 2008 r2 linked tables and views.  I have a continuous form with 7 fields for each row to use as a display for budget items.  The first 3 fields come from a budget table.  The 4th field comes from a view.  The remaining fields are calculated fields, such as percent, difference, etc.

    My difficulty is coming in the 4th field which comes from the view.  I have been trying to use DSum but that wasn't working.  I went to use a sql select sum statement and I got the NAME? in the field.  The code for both tries is posted below.  One of the criteria of the 4th field is the accounting number in field 1 (me.txtItemNumber.value).  This is a text since some have a letter in it.  Sample accounting numbers are 5010, 5040-A, 6030, and so on, which is why the field is text.  

    In the code below it is the criteria in the dsum >>> AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' where is seems to fail the most.  If I take the sql statement to the query design of Access and paste it and change YearID = 1 and the above line to use '6030' and run it, it returns back the correct number.  

    The code is behind a button.  It works until the DSum or below it is reached.  I am in a quandry over this. So any assistance given is appreciated.  Or if there is a better way of doing this, I'm open to suggestions.

    ...John

        'filling the budget information view only form
        Dim sqlBudget As String
        
        Me.RecordSource = ""
        
        sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount " & _
                        "FROM dbo_vwBudget " & _
                        "WHERE IsRemove = 0 " & _
                            "AND YearID = " & Me.cboYearID.Value & " " & _
                        "ORDER BY LineItemAccountingNumber  ASC"
        
        Me.RecordSource = sqlBudget
        
        Me.txtItemNumber.ControlSource = "LineItemAccountingNumber"
        Me.txtLineItem.ControlSource = "BudgetCategoryLineItem"
        Me.txtBudgetAmount.ControlSource = "BudgetAmount"
    
    '    Me.txtAmountUsed.ControlSource = DSum("TransactionAmount", "dbo_vwTransaction", "[Remove] = 0 AND [YearID] = " & Me.cboYearID.Value & " AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' ")
    
    
        Dim sqlUsed As String
    
        sqlUsed = "SELECT Sum(TransactionAmount) " & _
                    "FROM dbo_vwTransaction " & _
                    "WHERE Remove = 0 " & _
                        "AND YearID = " & Me.cboYearID.Value & " " & _
                        "AND LineItemAccountingNumber = '" & Me.txtItemNumber.Value & "' "
    
        Me.txtAmountUsed.ControlSource = sqlUsed

    If you're going to do it via DSum in a controlsource, you need to assign the DSum expression as a string to the txtAmountUsed.ControlSource, like this:

    Me.txtAmountUsed.ControlSource = "=DSum('TransactionAmount', 'dbo_vwTransaction', '[Remove] = 0 AND [YearID] = [cboYearID] AND [LineItemAccountingNumber] = [txtItemNumber]')"
    

    I'm not sure I have that perfectly right, but something like that. I think -- though I haven't verified this -- that Access will be able to fill in the values from [cboYearID] and [txtItemNumber], so long as they are controls on the form.

    An alternative would be to construct your recordsource SQL statement so that the single statement brings back the summed field as well.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by David_JunFeng Tuesday, November 24, 2015 1:16 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 9:06 AM
    Monday, November 23, 2015 5:08 PM

All replies

  •     
        sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount " & _
                        "FROM dbo_vwBudget " & _
                        "WHERE IsRemove = 0 " & _
                            "AND YearID = " & Me.cboYearID.Value & " " & _
                        "ORDER BY LineItemAccountingNumber  ASC"
        
    ...
    
    '    Me.txtAmountUsed.ControlSource = DSum("TransactionAmount", "dbo_vwTransaction", "[Remove] = 0 AND [YearID] = " & Me.cboYearID.Value & " AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' ")
    
    
    

    Hi John,

    Two remarks:

    - You assign the result of DSum to a ControlSource. I think you only must use  Me.txtAmountUsed = DSum(...)

    - In constructing your sql_string, then I find it easier to see if you did not omit a separating space, if you place them in front of the lines:

    sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount _
              & " FROM dbo_vwBudget _
              & " WHERE IsRemove = 0" _
              & " AND YearID = " & Me.cboYearID.Value _
              & " ORDER BY LineItemAccountingNumber  ASC"
    

    Imb.

    • Proposed as answer by David_JunFeng Tuesday, November 24, 2015 1:16 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 9:05 AM
    Saturday, November 21, 2015 8:43 AM
  • Hello.  The Access 2013 front end is using sql server 2008 r2 linked tables and views.  I have a continuous form with 7 fields for each row to use as a display for budget items.  The first 3 fields come from a budget table.  The 4th field comes from a view.  The remaining fields are calculated fields, such as percent, difference, etc.

    My difficulty is coming in the 4th field which comes from the view.  I have been trying to use DSum but that wasn't working.  I went to use a sql select sum statement and I got the NAME? in the field.  The code for both tries is posted below.  One of the criteria of the 4th field is the accounting number in field 1 (me.txtItemNumber.value).  This is a text since some have a letter in it.  Sample accounting numbers are 5010, 5040-A, 6030, and so on, which is why the field is text.  

    In the code below it is the criteria in the dsum >>> AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' where is seems to fail the most.  If I take the sql statement to the query design of Access and paste it and change YearID = 1 and the above line to use '6030' and run it, it returns back the correct number.  

    The code is behind a button.  It works until the DSum or below it is reached.  I am in a quandry over this. So any assistance given is appreciated.  Or if there is a better way of doing this, I'm open to suggestions.

    ...John

        'filling the budget information view only form
        Dim sqlBudget As String
        
        Me.RecordSource = ""
        
        sqlBudget = "SELECT LineItemAccountingNumber, BudgetCategoryLineItem, BudgetAmount " & _
                        "FROM dbo_vwBudget " & _
                        "WHERE IsRemove = 0 " & _
                            "AND YearID = " & Me.cboYearID.Value & " " & _
                        "ORDER BY LineItemAccountingNumber  ASC"
        
        Me.RecordSource = sqlBudget
        
        Me.txtItemNumber.ControlSource = "LineItemAccountingNumber"
        Me.txtLineItem.ControlSource = "BudgetCategoryLineItem"
        Me.txtBudgetAmount.ControlSource = "BudgetAmount"
    
    '    Me.txtAmountUsed.ControlSource = DSum("TransactionAmount", "dbo_vwTransaction", "[Remove] = 0 AND [YearID] = " & Me.cboYearID.Value & " AND [LineItemAccountingNumber] = '" & Me.txtItemNumber.Value & "' ")
    
    
        Dim sqlUsed As String
    
        sqlUsed = "SELECT Sum(TransactionAmount) " & _
                    "FROM dbo_vwTransaction " & _
                    "WHERE Remove = 0 " & _
                        "AND YearID = " & Me.cboYearID.Value & " " & _
                        "AND LineItemAccountingNumber = '" & Me.txtItemNumber.Value & "' "
    
        Me.txtAmountUsed.ControlSource = sqlUsed

    If you're going to do it via DSum in a controlsource, you need to assign the DSum expression as a string to the txtAmountUsed.ControlSource, like this:

    Me.txtAmountUsed.ControlSource = "=DSum('TransactionAmount', 'dbo_vwTransaction', '[Remove] = 0 AND [YearID] = [cboYearID] AND [LineItemAccountingNumber] = [txtItemNumber]')"
    

    I'm not sure I have that perfectly right, but something like that. I think -- though I haven't verified this -- that Access will be able to fill in the values from [cboYearID] and [txtItemNumber], so long as they are controls on the form.

    An alternative would be to construct your recordsource SQL statement so that the single statement brings back the summed field as well.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by David_JunFeng Tuesday, November 24, 2015 1:16 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 9:06 AM
    Monday, November 23, 2015 5:08 PM