Total a Group Where Textbox source is a Function

[GPnew]

I am trying to total a column (at group level) on a report
where the textbox in that column is populated by a function.

eg ControlSource:=DaysFunction([StartDate],[EndDate],[Beginning Date])

I have tried =sum(DaysFunction([StartDate],[EndDate],[Beginning Date])) however get an 'Unknown Function' error message when I run the report.
The function otherwise works fine without the Sum textbox on the report

I also tried =Sum([DaysCount]) where DaysCount is the name of the Textbox however this just cause a request for a value for [DaysCount] ie as per a parameter query when I run the report


Any Ideas ?

Thanks

 

[dhookom]

Your expression
=sum(DaysFunction([StartDate],[EndDate],[Beginning Date]))
should work. Is DaysFunction() in a standard module? Does it return a numeric value? How does it handle null values?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GPnew]

Duane

its a function that's code is imbedded/attached to the report that uses it (in a Report Access Class Object raher than in a module ) , it produces an integer result , it doesn't specifically deal with null values

 

[dhookom]

I would try move the code to a public module. Does the function specifically include:
Function DaysFunction(....) as Integer
If you use a public function, you could try create a new calculated value in the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GPnew]

Thanks very much Duane

Moving it to a module worked.

FYI it was Function DaysFunction(....) as Integer
(of course now it is Public Function ....etc)

I have another similar problem trying to total (=sum) another field on the same report
The formula in the textbox I am totalling is long =IIf(IsNull([txtTotalRepayable]) Or [txtTotalRepayable]="",0,([InterestRate]*[DayCount]*[txtTotalRepayable]/365))
AND the formula in the [txtTotalRepayable]is even longer
So the sum function is huge and I can't get it to work by putting the control source from [txtTotalRepayable]into the
above formula for the = sum( ....

Is there a limit on the number of characters in a formula
Concatenating all this as above in a =sum textbox is messy ....is there a way around it ?

 

[dhookom]

There is a limit to the number of characters in a control source. Can you calculate with your expression in the query rather than in a control source of a text box?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.