SUM statement

[JonyBravo]

On the following code I want to attribute the value of the sum([price]) to an variable called txtFullPrice:

Is there anybody that could help me please.

Dim dbs As Database, rst As Recordset
Dim strcriteria As String

'return reference to current database
Set dbs = CurrentDb

'Define serach criteria
strcriteria = "SELECT SUM([Price]) FROM tblRBS WHERE [USERID]='" & FOSUsername & "'"

'Create a dynadet-type Recordset object based on tblUser table
Set rst = dbs.OpenRecordset(strcriteria)

rst.Movelast

*******

'close connection on tblUser table
rst.Close
Set dbs = Nothing

This Forum have been helping me to improve my skills a lot. Thanks to everyone.

Jony Bravo

 

[Matt Greatorex]

Firstly, is txtPrice
1)a string variable (based on the txt part),
2)a numeric variable or
3) a text box on a form/report?
I know you said variable, but I wanted to double-check.

If it's a string, you can't set it to be a sum.

Assuming it's a number, try:

Dim txtPrice as Integer
txtPrice = DSum("[Price]","tblRBS","[USERID]='" & FOSUsername & "'")

Assuming it's a text box, try

[forms]![formname]![txtPrice] = DSum("[Price]","tblRBS","[USERID]='" & FOSUsername & "'")

Hope that's of some use.

 

[JonyBravo]

Hy!
Thanks to help. DO you mean something like it?

Dim dbs As Database, rst As Recordset
Dim strcriteria As String

'return reference to current database
Set dbs = CurrentDb

'Define serach criteria
strcriteria = "SELECT SUM([Price]) FROM tblRBS WHERE [USERID]='" & FOSUsername & "'"

'Create a dynadet-type Recordset object based on tblUser table
Set rst = dbs.OpenRecordset(strcriteria)

rst.Movelast

[forms]![formname]![txtPrice] = DSum("[Price]","tblRBS","[USERID]='" & FOSUsername & "'")


'close connection on tblUser table
rst.Close
Set dbs = Nothing

 

[Matt Greatorex]

Hy!

[forms]![formname]![txtPrice] = DSum("[Price]","tblRBS","[USERID]='" & FOSUsername & "'")

Just to put that value into the textbox you named, this line should be enough on it's own.

 

[JonyBravo]

Hy!
This worked perfect. Thanks very much.


Jony Bravo

 

[Matt Greatorex]

Thanks for letting me know.

You're very welcome.