Sum in Query Values

  • Thread starter Thread starter Michael C
  • Start date Start date
M

Michael C

I have a query and I'm trying to sum a row of values and
am getting an error: Compile Error, Sub or Function not
defined...and it points to the word "Sum" below. Here's
the vba:

Set db = CurrentDb

Set rsData = db.OpenRecordset("SELECT * " & _
"FROM [qryData]")

MTDValue = Sum(rsData![T])
MTDPlanValue = Sum(rsData![TPlan])
MTDPercPlan = MTDValue / MTDPlanValue

Is there some other way to use the sum the values? Any
suggestions would be great. Thanks.
 
The DSum is exactly what I was looking for. Thanks!!!

-----Original Message-----
I have a query and I'm trying to sum a row of values and
am getting an error: Compile Error, Sub or Function not
defined...and it points to the word "Sum" below. Here's
the vba:

Set db = CurrentDb

Set rsData = db.OpenRecordset("SELECT * " & _
"FROM [qryData]")

MTDValue = Sum(rsData![T])
MTDPlanValue = Sum(rsData![TPlan])
MTDPercPlan = MTDValue / MTDPlanValue

Is there some other way to use the sum the values? Any
suggestions would be great. Thanks.
Click to expand...

You're mixing languages: this is NOT a query, it's VBA code. The Sum()
operator works in Totals Queries - you could for instance have a
query

SELECT Sum([T]) AS SumOfT, Sum([TPlan]) AS SumOfTPlan,
Sum([T])/Sum([TPlan]) As PercPlan FROM qryData;

to generate a new one-record recordset with the sums and the
percentage - and of course you could then open THIS recordset.

Or, you could not open the recordset at all, and instead use the DSum
function:

MTDValue = DSum("[T]", "[qryData]")
MTDPlanValue = DSum("[TPlan]", "[qryData]")




.
Click to expand...
 
SELECT Sum([T]) AS SumOfT, Sum([TPlan]) AS SumOfTPlan,
Sum([T])/Sum([TPlan]) As PercPlan FROM qryData; ....

Or, you could not open the recordset at all, and instead ....

MTDValue = DSum("[T]", "[qryData]")
MTDPlanValue = DSum("[TPlan]", "[qryData]")
MTDPercPlan = MTDValue / MTDPlanValue
Click to expand...

The DSum is exactly what I was looking for. Thanks!!!
Click to expand...

Do bear in mind that

DSum("[T]", "qryData") / DSum("[TPlan]","qryData")

is not the same as

DSum("[T] / [TPlan]", "qryData")

and only one of them will give you the correct result.... :-)


Tim F
 
Back
Top