Create a sum of records in an open recordset(Access97)

[Reiner Harmgardt]

Hi NG

I have set up a recordset like this.

SQL = ""
SQL = SQL & " SELECT [Contracts
Controlling].[Select]"
SQL = SQL & " ,[Contracts
Controlling].Flow"
SQL = SQL & "
,Sum(manuell_DCF.amount) AS SumOfChf"
SQL = SQL & " ,manuell_DCF.CY
AS NewCY1"
SQL = SQL & " ,manuell_DCF.CM
AS NewCM1"
SQL = SQL & "
,manuell_DCF.onoff"
SQL = SQL & "
,DCF_Alea.ALEA_Nr"

SQL = SQL & " FROM (([Contracts
Controlling] "

SQL = SQL & " LEFT JOIN manuell_DCF "
SQL = SQL & " ON [Contracts
Controlling].Contract = manuell_DCF.Contract) "

SQL = SQL & " LEFT JOIN Connecting_DCF_ALEA "
SQL = SQL & " ON manuell_DCF.DCF =
Connecting_DCF_ALEA.[DCF-Nr])"

SQL = SQL & " LEFT JOIN DCF_Alea "
SQL = SQL & " ON
Connecting_DCF_ALEA.[Alea-Nr] = DCF_Alea.ALEA_Nr"

SQL = SQL & " GROUP BY [Contracts
Controlling].[Select]"
SQL = SQL & " ,[Contracts
Controlling].Flow"
SQL = SQL & " ,manuell_DCF.CY "
SQL = SQL & " ,manuell_DCF.CM"
SQL = SQL & "
,manuell_dcf.onoff "
SQL = SQL & " ,DCF_Alea.ALEA_Nr
"

SQL = SQL & " HAVING ((([Contracts
Controlling].[Select]) = -1) "
SQL = SQL & " And ((manuell_dcf.onoff)
= 1) "
SQL = SQL & " And ((DCF_Alea.ALEA_Nr)
= ""90"")) "

SQL = SQL & " ORDER BY manuell_DCF.CY"
SQL = SQL & " ,manuell_DCF.CM"


Set rst = DB.OpenRecordset(SQL)

How can create a sum of certain records in this recordset?

I tried DSUM but it couldn't make it work.

(criteria = "[NewCM1] = " & Monat & " and [NewCY1] = " & Jahr & "")

(xSum = DSUM("SumOfCHF", rst, criteria) error = 13 type mismatch
(xSum = DSUM("SumOfCHF", rst) error = 13 type mismatch
(xSum = DSUM("SumOfCHF", SQL) error = 2048 You entered an
invalid argument in a domain aggregate funktion

Background:
I don't want to use a loop, because that's what's already in use.
The idea behind it is that i'm trying to speed up the work a little
bit by trying to create a sum instead of looping every time.

Is there a solution for this problem?

Thanks for helping.

Regards

Reiner