Null values

B

Bell

I'm having the follwing expression in a query

B0501C: Sum(IIf([Category]="Budget" And [Period]="200501";[Amount]))

but what do I add to this expression, if I want Access to return 0 instead
of null in cases when there are no values meeting the IIf statement?

Nz function have been suggested by some but I don't know how to fit it into
the expression.

Best regards / Bell
 
O

Ofer

Hi again

Try this

Sum(IIf([Category]="Budget" And [Period]="200501";nz([Amount],0),0))
 
B

Bell

Hi,

thanks for your reply, I tried it but I get an invalid syntax error so i
tried it on my quarter sum instead like

BQ1: Nz([B0501];0)+Nz([B0502];0)+Nz([B0503];0)

and it worked perfectly.

Thank you Ofer / Bell

"Ofer" skrev:
Hi again

Try this

Sum(IIf([Category]="Budget" And [Period]="200501";nz([Amount],0),0))


Bell said:
I'm having the follwing expression in a query

B0501C: Sum(IIf([Category]="Budget" And [Period]="200501";[Amount]))

but what do I add to this expression, if I want Access to return 0 instead
of null in cases when there are no values meeting the IIf statement?

Nz function have been suggested by some but I don't know how to fit it into
the expression.

Best regards / Bell
Click to expand...
Click to expand...
 
Top