Problem with IsNull in Access Report

[Farzam]

Dear all,
Iam using back SQL server 2000 front ADP,
in access report i cant use the IsNull function
in access form I successfully used it.
but in report it gives me the error of ...

("The expression 'Sum(IsNull([Amount])) is invalid
Aggregate functions are only allowed on output fileds of the record source")

that is the message any one can help of any way...

regards,

 

[Remou]

Try Nz or IIf:

Sum(IIf([Amount] Is Null,1,0))

Sum(Nz([Amount],1))

It is a little difficult to see what you want. I wonder do you wish to count Nulls?

PS There is an ADP forum and an Access Reports forum.

 

[Farzam]

Actually, I am going to Sum up the amount
which contains some null values

I have used this successfully in the Forms of Ms Access Project back SQL Server

but in report it shows #error

I dont know why?
my formula is like =IsNull(figure1)+Isnull(figureb)

that is simple working in forms but not in report

any trick ??
thanks

 

[Remou]

On a form in Access, this:

=IsNull(figure1)+Isnull(figureb)

Will return either 0 or -1 (true or false) for each IsNull.

It is usual to use Nz:

=Nz(figure1)+Nz(figureb)

Nz will also suit in a report.

It is not necessary to check for nulls when using Sum in a report or group footer to get a total.