Need help on a Query Calculation

[Eric]

I'm trying to figure out how I can create a query that would do the following
calculation. Any help would be much appreciated.

My problem lies around the fact that I'm creating sort of a number "pyramid"
where each of the levels of the "pyramid" represent a number. In order to
describe the "pyramid", I've created 3 fields in a table. The first field is
labeled as "baselevel", the second field is labeled as "levelmultiple" and
the third field is labeled as "toplevel".

The "baselevel" field will indicate what number the "pyramid" should start
with (for instance: 1), the "levelmultiple" field describes how to get from
the "baselevel" to the next level (for instance, if the "levelmultiple" has a
value of 1 in it, the second level of the pyramid would be 1+1 = 2, the third
level would be 2+1 = 3). The "toplevel" field indicates the top value of the
"pyramid" (for instance 5).

What I'm trying to do is add up all the levels in the field. So if the
"pyramid" is a
1-5-1 where the base number = 1, the top number = 5 and each level of the
"pyramid" has an interval of 1, I want to add 1+2+3+4+5+4+3+2+1 = 25. How
would I solve this if I didn't know what values would be entered into the
field.

Thank you in advance
Eric

 

[John Spencer]

Perhaps this VBA function will do what you want.

Public Function fGetTotal(Lbase As Long, Ltop As Long, Llevel As Long)
Dim Lsum As Long
Dim iLoop As Long

For iLoop = Lbase To Ltop Step Llevel
Lsum = Lsum + iLoop
Next iLoop

Lsum = 2 * Lsum - Ltop
fGetTotal = Lsum

End Function