Iif.......

[SteveF]

Days Stock: [Day Stock]/[Closing @ Cost]

This expression is returning me an error if one of the numbers is '0', I really need it to return a '0'. I have managed to get an IIF statement to work in another part of the same query but for some reason the same format won't work here.

This is the one that works:

GP%: IIf([SumOfCons Ex VAT]<>0,[SumOfGP Amount]/[SumOfCons Ex VAT],0)*100

So my (no doubt faulty) logic told me that this would work:

Days Stock: IIF([Day Stock]<>0,/[Closing @ Cost],0)


Where am I going wrong? As ever, your genius is appreciated..

 

[maxmangion]

in the true part of your iif statement you only have "/[Days]" so what are you dividing the [Days] with ?

 

[SteveF]

Sorry, I just realised what a horlicks I made of asking that question so have edited it. The first part is the number of days and is itself a result of a query, the second part is the number of days calculated by a query.

What it does is calculate the consumption per day and multiply it out by the stock on hand to tell us how many days' stock we have. If one or the other number is '0', the query returns an error, I need it to return a '0'.

I'm sure it's me being dumb, I'm very green at this Access thing...

 

[maxmangion]

IIF([Day Stock]<>0,/[Closing @ Cost],0)

The true part of the iif is certainly wrong ... what are you dividing the [Closing @ Cost] with? ... you need to have something before the "/" in the true part.

 

[SteveF]

I'm dividing the Day Stock by the Closing @ Cost, so that's the whole equation. If both of those fields have a value it works in the way I want it to, it returns the correct number for me if I just run it in the original format (it will work both ways around):

Days Stock: [Closing @ Cost]/[Day Stock]

If one or the other is '0' then this format returns an error, so I'm trying to get an IIF statement in there to tell it to return '0' rather than an error.

 

[maxmangion]

hi,

check out this link

 

[SteveF]

Thanks for the link, it hasn't helped me a great deal but thanks.

 

[khawar]

Just Use this

Days Stock: iif([Closing @ Cost]<>0,[Day Stock]/[Closing @ Cost],0)

 

[SteveF]

Brilliant! Thanks