Another iif problem

[blindlemonray]

I didn't realise how many people had issues with the iif statement until I searched!!

Anyway does anybody know why this does not work in a iif statement...

IIf(IsNull([elec]),"",Sum([debit])/Sum([elec]))

 

[lupins46]

Can you explain in words what you are trying to do and where you are trying to do it.

 

[blindlemonray]

no probs,

two fields in the table... 'elec' and 'debit' I need to get a percentage of the 'debit' against the 'elec' but if the 'elec' field is null then return a zero value not an error.

 

[blindlemonray]

forgot to say...

several entries for each day and need to sum then all to create a total

 

[lupins46]

So does this work (you don't say where you are trying to do this):

iif(nz(elec)=0,0,debit/elec)

 

[blindlemonray]

No, I thought about the nz function but still no joy, its ok but it doesn't seem the like the bit where I ask...
==========================
Sum([debit])/Sum([elec]))
==========================
can you not do this in a iif statement?

 

[PHV]

And this ?
Sum(IIf(IsNull(elec),0,debit/elec))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blindlemonray]

I just get an overflow error. Could run it through a couple of queries but I was just seeing if it was possible to do it in one!

 

[PHV]

And this ?
Sum(IIf(Nz(elec,0)=0,0,debit/elec))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blindlemonray]

Hi PH,

no joy, this perform a the sum for each day and then sums them into a total giving a false figure. What I need to do is opposite way round. sum the figures for each day they divide the two against each other giving you an overall percentage

Found a way round it though, when data is uploaded ignore '0' and leave field null, this in turn stops any error being made.

thanks for your time on this one PH

BLR