Using Dsum in a VBA query

[grenee]

Good Day All,

I am seeking to find a way to sum a field in a query generated in VBA.

Using DSum works well if the query already exist and is simply called in VBA. However when the query is generate in VBA the DSum function does not work. Here is my code to explain my situation:

Private Sub YearByMonths_Click()
Dim curDatabase As DAO.Database
 
    Dim rst2  As DAO.Recordset
    
   
   Set curDatabase = CurrentDb
   
  
 Set rst2 = curDatabase.OpenRecordset("YearMonthSales")
 
  MsgBox DSum("Sales", "[Sales Analysis]")
  MsgBox DSum("Sales", "[rst2]")
  rst2.Update
 
Set rst2 = Nothing
Set curDatabase = Nothing
  
End Sub

The DSum("Sales", "[Sales Analysis]") works because the "Sales Analysis" exist.
But the DSum("Sales", "[rst2]") does not work as it is created in this subroutine.

Would be grateful for assistance in explaining my challenge

 

[Cronk]

The syntax is
dsum("tableOrQueryName","fieldname",[criteria])
If fact your dsum is trying to sum the values of the non existent field [rst2] in the Sales table.

If your query is being generated in vba, then you can create and save a new query based on your vba sql,

or having opened the recordset, loop through it to get the accumulated sum of your required field,

or change your make your vba query and aggregate query which returns the sum.

 

[MarkK]

Try this...

Private Sub YearByMonths_Click()
    MsgBox DSum("Sales", "[Sales Analysis]")   
    MsgBox DSum("Sales", "YearMonthSales")
End Sub

Looking at your code, there was no need to open the recordset.
hth
Mark

 

[Galaxiom]

The syntax is
dsum("tableOrQueryName","fieldname",[criteria])

First two arguments are the other way around.

BTW The first argument doesn't have to be a fieldname. It is actually an expression that may include functions and multiple fieldnames.

 

[Cronk]

Um, yes. (some more characters to comply with system requirements that the post be at least 10 characters)

 

[arnelgp]

You dont use dsum on recordset. You use dsum and other D function on table and select queries.