Totals Query VBA

Oreynolds

Member
Local time
Today, 10:58
Joined
Apr 11, 2020
Messages
166
Hi, I have a totals query in VBA which has only 1 column which creates a single totalled value.

Can anyone advise how I can then take this value and then assign it to a variable so I can use it elsewhere?

Thanks
 
Dlookup if you don't mind some type conversion, Dsum if you do

But what do you mean specifically by "in VBA" ?
 
You have a query in VBA? What does that look like? You may have to use a recordset.
 
Throw away the VBA. If you want to use the SUM of a column some where just use a DSUM:

 
Thanks. The problem is that I have to calculate about 12 values and am currently already using a DSUM for each which works fine. The reason I was looking to try it with a query was to see if speed the process up.....
 
You may use Tempvars (temporary Global Variables) to store the value and use it in TextBoxes on Forms, Reports, or in VBA.
Example Syntax: Tempvars.Add VariableName, Value

Tempvars.Add "Total",DSum("Amt","QueryName")

To display the Value in Variable 'Total' - assuming the value in variable Total= 1000

? Tempvars!Total
result: 1000

To use the value in a TextBox on a Form as part of an expression.
="Average: " & Tempvars!Total/DCount("*","QueryName")

To remove a specific variable from memory:
Tempvars.Remove Total

To remove all the Tempvars from memory.

Tempvars.RemoveAll

You can assign value to Temporary Variable through Macro Command: SetTempvars "VariableName", Value

You can define several global temporary variables this way and stays valid till you shutdown the database or till you execute individual Remove command or RemoveAll command to remove all the active variables in memory.
 

Users who are viewing this thread

Back
Top Bottom