Sum function with variable range

[bobfin]

I have 10 queries. Each uses the same table (tblAmt) and gets a sum from the same field (Amount). The only difference between the queries is that the Amount field is restricted to a different range in each query. I then use another query to collect all 10 sums for use in a report.

I want to create a function that uses 2 parameters (the Start and End values of the range) and results in the correct sum. That way I could use a single query where the columns are the function with the various parameter values supplied. I know I need a string variable that defines the SQL of the query, but I’m confused about using code with Recordsets, TableDefs, etc. Any sample code would be greatly appreciated.

 

[KenHigg]

I'm not sure I follow what you're doing but couldn't you use a dsum()?

kh

 

[bobfin]

I could, but there are 2 reasons I don't want to use that approach. First, the domain aggragate function use more overhead than a compiled function and I have 3 more collections of queries that I need to replace. Second, I want to learn how to use VBA to manipulate recordsets, queries, etc.