getting sum for continuous field

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

Answer 1

    
    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.

Answer 2

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