Sum with expression in form footer returns #error

[Guest]

Hi ,
in my access project (adp) I've bound a form to a SQLServer table and create
a control in the forms footer.
The controlsource is:
=Sum(([zeitaufwand]*[internerkostensatz]))+Sum([externe_kosten])
[zeitaufwand], [internerkostensatz] and [externe_kosten] are tablecolumns.

When I open the form the control displays #Error.

However, when import the same form into a Access database (.mdb) and bound
it to the ODBC linked table the control displays the expected result.

Access 2002 version is (10.6501.6714) SP3

Anyone has any ideas how to troubleshoot?

 

[Robert Morley]

Try removing one set of parentheses, first of all. I don't think it's that,
but certainly won't hurt. It might also be trying to process this
server-side and failing, or maybe just using incorrect syntax due to a bug.
Try forcing it to process client-side by adding a user-defined function to
it (or one that's only available in Access). For example, in some module,
add the following:

Public Function One() As Long
One = 1
End Function

The change the ControlSource to:
=SUM([zeitaufwand]*[internerkostensatz]*One())+SUM([externe_kosten])


If that fails, you can also try:

Public Function Multiply(ByVal a As Currency, ByVal b As Currency) As
Currency
'I'm assuming you'll want Currency above, based on your field names, but
you can use Double, or Long, or whatever instead.
Multiply = a * b
End Function

ControlSource:
=SUM(Multiply([zeitaufwand], [internerkostensatz])) +
SUM([externe_kosten])

Let me know how those work, and if they don't, I'll look into it on my copy
of Access...right now, I'm just doing this off the top of my head.



Rob

 

[Sylvain Lafontaine]

http://support.microsoft.com/?id=225992

 

[Guest]

Thanks Robert and Sylvain for the clues and spending your time with this.

I followed Microsoft kb article id 225992 and was able to solve the problem
by adding a calculated column to the forms datasource.
Now the total sum is calculated in the forms footer.

Again many thanks!

Regards
Bodo