Totals Query VBA (1 Viewer)

[Oreynolds]

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]

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

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

 

[theDBguy]

You have a query in VBA? What does that look like? You may have to use a recordset.

 

[plog]

Throw away the VBA. If you want to use the SUM of a column some where just use a DSUM:

MS Access: DSum Function

This MSAccess tutorial explains how to use the Access DSum function with syntax and examples. The Microsoft Access DSum function returns the sum of a set of numeric values from an Access table (or domain).

www.techonthenet.com

 

[Oreynolds]

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]

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.