Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access Sum not working as control source

Hi

I have an Access Report where the control source "[Invoice Details].[Amount]" works on a control but  "=Nz(Sum([Invoice Details].[Amount]),0)" does not.
It causes an error. This makes no sense to me.

Thanks
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Hi,

Is the control bound ?
Did you try with an unbound control and a query ?
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Maybe it needs to be:

=Sum([Amount])
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Correct but in that case, you will have to use Nz() if Field1 or Field2 might be null because  because Access performs the addition operation first and that will fail if either field is null event though the sum() handles nulls by ignoring them.

Sum(Nz(Field1,0) + Nz(Field2,0))
As a side note  I was assuming [Amount] is the Field name ....
Avatar of Murray Brown

ASKER

Thanks for the advice