CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

How to sum column values of a select query

I am trying to display the "total of all values" in the column NetPrice to a textbox which is in the form footer if a user chooses items in the two combo boxes. This should be true for only the displayed records. I don't know how to go about this and hope someone has done this before. I would really appreciate some help. 

Private Sub comboPeriod_AfterUpdate()
Dim strSQL As String

If Me.comboPeriod.Value = "Q1" And Len(Me.comboPostCode.Value & vbNullString) <> 0  Then
   strSQL = "SELECT * FROM qrySpending WHERE (Month(orderDate) = 4 OR Month(orderDate) = 5 OR 
   Month(orderDate) = 6) AND (PostCode = '" & Me.comboPostCode.Value & "') ORDER BY Description ASC"

   Me.frmDatasheet.Form.RecordSource = strSQL
   Me.frmMain.Form.Requery

   Me.txtNetPrice.Value = DSum(Nz("NetPrice", 0), "strSQL")
Exit Sub
End If

End Sub

Upvotes: 0

Views: 140

Answers (1)

June7
June7

Reputation: 21370

Domain aggregate functions cannot reference SQL statement, only table and query objects. Do aggregate calc in a grouping query which can be the SQL statement in your code. However, SQL statement is not really needed for this. Simply use filter criteria in DSum() in textbox or VBA.

DSum("NetPrice", "qrySpending", "Month(orderDate) = 4 OR Month(orderDate) = 5 OR Month(orderDate) = 6) AND (PostCode = '" & Me.comboPostCode & "'")

Upvotes: 1

Related Questions