queries returning null instead of 0 3

[Zelandakh]

I have a load of columns I need to sum. Then I need to sum the lot for a grand total. If there are no entries in the column, the sum returns null rather than zero and the percentages are blank. Grand total goes blank too.

Need something like =iif(isnull(fieldname),0,fieldname) but that doesn't work.

I'm missing something obvious...

 

[Mighty]

I think that you are on the right track. I had a similar problem where my sum was ignoring rows that had a null value. I used the following:

sum(IIF("Part Stock".NETFLG_06='N' OR isNull("Part Stock".NETFLG_06),0,"Part Stock".QTYOH_06)) as QTYOH

This works for me. Hope this helps. Mise Le Meas,

Mighty

 

[Eldaria]

Easy, I had this, and what you have to do is make a new Query that links back to the first Query. then you make your iif statement.

You have the field you want to find out the value of.
then you makie a Expression field. and there you type your iif statment. mine looks like this:

AverageVaule: IIf(IsNull([AvgOfSS]);0;[AvgOfSS])

In this way the Value will show 0 if there is no Value on the AvgOfSS field, but if it has a value, then it will show that value.
Eldaria

http://www.eldaria.net

That was my 25cent** of opinion.

** Inclusive Intrest, tax on interest, Genral tax, Enviromental tax, Tax, and tax on intrest, tax on fees, tax on tax, and other Various taxes and fees.

 

[Zelandakh]

Followed Eldaria's idea of nested queries - works first time.

Knew I was close!