Calculating totals in a Subform based on a condition

[TimS]

Hi,

Hope someone can help me here.

I have data on central SQL Server - and using Access 2003 as front end via
ODBC. I have a form with a subform datasheet that adds items up in the
subform based on a condition e.g. =sum(iif(X,[Number],0), that sums the field
[Number] when X is true.

This works fine under a .mdb database.

I am doing my best to move to a .adp database - this approach does not work
here .... I do not seem to be able to figure this one out - I do not want to
add columns to the underlying view - as this is prohibative due to the number
required, and makes maintainance very painful.

I find that even =sum([Number]*1) does not work in the .adp database .....
it seems not to like anything except just adding everything up in the column.

Any thoughts on this how to solve this one ???

Thanks

Tim

 

[Michel Walsh]

Hi,



try


SUM( CASE WHEN x THEN fieldName ELSE 0 END CASE)



where x is a test (there is no Boolean data type in MS SQL Server, it is
preferable to make a full test, like bitFieldName = 1; while Jet converts
a not zero value to True, don't relay on that behavior within MS SQL
Server ).


Hoping it may help,
Vanderghast, Access MVP