Totals Query VBA (1 Viewer)

Oreynolds

Member
Local time
Today, 05:59
Joined
Apr 11, 2020
Messages
157
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
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:59
Joined
Mar 14, 2017
Messages
8,738
Dlookup if you don't mind some type conversion, Dsum if you do

But what do you mean specifically by "in VBA" ?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:59
Joined
Oct 29, 2018
Messages
21,358
You have a query in VBA? What does that look like? You may have to use a recordset.
 

plog

Banishment Pending
Local time
Today, 00:59
Joined
May 11, 2011
Messages
11,613
Throw away the VBA. If you want to use the SUM of a column some where just use a DSUM:

 

Oreynolds

Member
Local time
Today, 05:59
Joined
Apr 11, 2020
Messages
157
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.....
 

apr pillai

AWF VIP
Local time
Today, 11:29
Joined
Jan 20, 2005
Messages
735
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

Top Bottom