DSum Question 2

[FontanaS]

Hello,

I have a text field on the report with the following dsum function in it

=DSum("[AmountReq]","Qry_Main","strWhereClause2 & [Approp] = 'F&E'")

I keep getting an "error#". The filed strWhereClause is a condition that is generated in code and varies each time depending on what the user selects on the form. So i create the where part and assign it to the variable strWhereClause.

I think the syntax is wrong, but do not know if you can have variables in the dsum function.

Any help is greatly appreciated

 

[mndrlion]

strWhereClause2 does not need to be inside the parens. Try it this way:

=DSum("[AmountReq]","Qry_Main",strWhereClause2 & " [Approp] = 'F&E'")

 

[FontanaS]

Thanks!

When I try to run the report - an input box appears asking for the value of strwhereclause2, when i go to look at the code in the field, access is putting brackets around the variable (=DSum("[AmountReq]","Qry_Main",[strWhereClause2] & " [Approp] = 'F&E'")))

an example of the strwhereclause would be -

[Office] = 'ACX-030' And [FundStatus] = 'Not Funded'

 

[RoyVidar]

What exactly is strWhereClause2?

Your indicating it's a variable, then I don't think it can be used directly in a controls controlsource, but needs to be fetched thru a public function (in a module, not a forms/reports module).

[tt]public function getstrWhereClause2()
getstrWhereClause2=strWhereClause2
end function[/tt]

Then use the function in the controlsource:

[tt]=DSum("[AmountReq]","Qry_Main",getstrWhereClause2() & " AND [Approp] = 'F&E'")[/tt]

I think also you'd need to put in an AND or OR between the strWhereClause2 and the last criterion (se above).

Roy-Vidar