Sum values

[Abby N]

I'm creating a user-defined function. As part of this function I need to sum the total values of a field, [Qty]. However, I can't figure out how VB sums values over multiple records. The following code produces an error message "Sub or function not defined."

Dim intResult As Integer
Dim vrQty As Variant
vrQty = Me.Qty
intResult = Sum(vrQty)

Does VB not support the 'sum' function or am I missing a reference as the help file suggests?



[This message has been edited by Abby N (edited 08-30-2000).]

 

[Pat Hartman]

The Sum() function is only available in SQL or in the controlsource of a field in a form or report.

You would need to use DSum() in VBA - look up the syntax.

 

[Abby N]

I was afraid of that. SQL is my weakness. I have almost no experience with it. I'd considered DSum. However, it seemed too limited for my purposes. The code in my first post was only meant to be an example of my problem. But, as I'm trying to code this as a function that can later be used in queries and in ControlSource, I don't see how I can make DSum do what I'm after. Here's the actual function as I envisioned it. Any suggestions on how to make this work?

Function Forecast(numPoint As Variant, numKnownY As Variant, numKnownX As Variant) As Double
Dim numNumOfPoints As Double
Dim valA
Dim valB
valB = (((numNumOfPoints) * Sum(numKnownY * numKnownX)) - (Sum(numKnownX) * Sum(numKnownY))) / ((numNumOfPoints * Sum(numKnownX ^ 2)) - (Sum(numKnownX) ^ 2))
valA = avg(numKnownY) - valB * avg(numKnownX)
numNumOfPoints = Count(numKnownX)
Forecast = valA + valB * numPoint

End Function