You must make a declaration of the variable name and type:
Dim vSumOfColumn as Long
Then the assignment of the value to the variable using the DSum function:
vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName"
This will SUM all of the values in tblFieldName name in tblYourTableName and assign that value to the variable vSumOfColumn.
Now you didn't say if you wanted all records to be selected and added together. There is a third parameter of the DSum function that is the criteria statement. This is used to select only certain records meeting a pre set value. The following statement adds that type of criteria statement to the function. Note that this is looking for records with the table field = 1000. This is how a numeric criteria statement is setup:
vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName", "[tblFieldNameXX]= 1000"
An alphanumeric is slightly different. Single quote marks must surround the value being compare to:
vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName", "[tblFieldNameXX]= 'Robert'"
Ok, that gives me some ideas... but what I really need to know is the proper way to load variables from the tables for use in calculations/field population/inserts/etc.
Since my post I have figured out:
Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset
SQLCode = "SELECT LTicketNum From TransIn"
Rs1.Open SQLCode, CurrentProject.Connection
Rs1.MoveFirst
Dim tester As Integer
Do Until Rs1.EOF
tester = Rs1!LTicketNum
MsgBox (tester)
Rs1.MoveNext
Loop
Rs1.Close
Set Rs1 = Nothing
--- not sure if this is the best way or not. I am used to Server Side Programming and used to do VB6 a few years ago. VBA seams to be just different enough to be anoying.
In the case of summing the column or other column functions like that, a query is probably the best way to go. The only way I know of to do it in VBA is by traversing the records using a recordset object. You can use a totals query to find the sum, then get the value from the query. You may need a recordset object to get it from the query, but you may be able to refer to the query directly.
Here's a quick way to do it.
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT SUM(uh3) FROM uh"
<variable> = rst.Fields(0).Value
rst.close
You can also use the CreateQueryDef method (something like that).
Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset
SQLCode = "SELECT COUNT(LTicketNum) As test From TransIn"
Rs1.Open SQLCode, CurrentProject.Connection
Rs1.MoveFirst
Dim tester As Integer
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.