Solved

# Get Sum from Field

Posted on 2009-02-10
722 Views
how to get sum from the field?
the code below shows to save the data.
Can you help me how to get the sum of the !amount?
``````Public Sub save_Deduction(cn As ADODB.Connection, lptxtCode As String, Description As String, Amount As String)

Dim sql As String

sql = "select * from Deduction where Code='" & lptxtCode & "'"

rs.Open sql, cn

With rs

If .BOF = True And _

.EOF = True Then

!Code = lptxtCode

End If

!Code = Trim(txtCode.Text)

!Description = Trim(txtDescription.Text)

!Amount = Trim(txtAmount.Text)

.Update

.Close

End With

Set rs = Nothing

End Sub
``````
0
Question by:Whing Dela Cruz

LVL 15

Expert Comment

Execute this query... It will return the Sum of the amount...

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction WHERE code = 'CODE'
0

LVL 15

Expert Comment

Dim sql As String
sql = sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction WHERE code = 'CODE'"
rs.Open sql, cn
With rs
msgbox (rs("Total Amount"))
End With
Set rs = Nothing
0

Author Comment

Hello Jack ofPH,

I think i'm very wrong with my application.. Please modify

``````Private Sub Command1_Click()

Dim sql As String

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction WHERE Amount = 'Amount'"

rs.Open sql, cn

With rs

' MsgBox (rs("Total Amount"))

text1.Text = (rs("Total Amount"))

End With

Set rs = Nothing

End Sub
``````
0

LVL 15

Expert Comment

Replace this:

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction WHERE Amount = 'Amount'"

With this one...

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction"
0

Author Comment

During Execution, An error Message appear " Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. then if i click debug
rs.open sql, cn is being HighligHted. what does it mean?
Is my code above is correct ? What shall i do?
0

LVL 15

Expert Comment

Private Sub Command1_click()

msgbox(GetTotalAmount)

end sub

Private Function GetTotalAmount() as decimal

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction"

GetTotalAmount = cn.executeScalar(sql)

end sub
0

LVL 15

Expert Comment

Private Sub Command1_click()

msgbox(GetTotalAmount(cn))

end sub

Private Function GetTotalAmount(Byval cn as Connection) as decimal

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction"

GetTotalAmount = cn.executeScalar(sql)

end sub
0

Author Comment

Hello JackofPh,

the command is not accepted in my computer it will become red when i execute...
Private Function GetTotalAmount(Byval cn as Connection) as decimal

0

LVL 15

Accepted Solution

Private Sub Command1_click()

msgbox(GetTotalAmount(cn))

end sub

Private Function GetTotalAmount(byval cn As ADODB.Connection) as decimal

sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction"

GetTotalAmount = cn.executeScalar(sql)

end sub
0

Author Closing Comment

Thanks! More Power!!
0

## Featured Post

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…