Iif Statement

[PanzerAttack]

Hi, I know there's lots of questions based around this and I'm still looking but haven't managed to find anything to exactly match my problem (although I'm sure it's easy).

I'm trying to recreate an Excel calculation using Iif.

In Excel I have:

D13 E13 F13 G13 CALC Field
100 50 25 120 =IF(MIN(D13,E13,F13)<=0,G13,(D13/E13*F13))

D/E & F can be zero or less and possibly null (although I can fix the null if needs be).

I'm having problems recreating this in Access, this doesn't seem to work:

IIf([D13]=<0,[G13], IIf([E13]=<0,[G13], IIf([F13]=<0,[G13], (([D13]/[E13] )*[F13])

Also, I don't have Access 2003 at home so if you know where I can download a trial version I could practice this before I get back to work.

thanks, hope it makes sense.

 

[pbaldy]

The most direct translation would be:

IIf(D13<0 OR E13<0 OR F13<0, G13, ([D13]/[E13] )*[F13])

 

[PanzerAttack]

That was very quick, thanks. I shall try when I get back to work.

If D13, E13 or F13 are Null, will:

IIf(IsNull(D13<0 OR E13<0 OR F13<0), G13, ([D13]/[E13] )*[F13])

simply work do you think?

 

[pbaldy]

No, you'd want to wrap each of the 3 tests in an Nz() function:

Nz(D13,0)

 

[PanzerAttack]

Thanks again, I can't test it as I don't have Access at home, so to be totally thick, you mean:

IIf(D13<0 OR E13<0 OR F13<0, G13, ([D13]/[E13] )*[F13]) becomes

IIf(Nz(D13<0) OR Nz(E13<0) OR Nz(F13<0), G13, ([D13]/[E13] )*[F13])

 

[pbaldy]

No, in retrospect my description didn't match my example very well. If you look at help on the Nz function you'll understand what it's meant to do. Try this:

IIf(Nz(D13,0)<0 OR Nz(E13,0)<0 OR Nz(F13,0)<0, G13, ([D13]/[E13] )*[F13])

By the way, you need to make sure E13 isn't zero either, as that would cause a divide by zero error.

 

[PanzerAttack]

I'm afraid E13 can be zero, I can ensure it's not Null but it's very likely to be zero. I shall read up on Nz. Although shouldn't it enter field G13 if it's zero?

 

[pbaldy]

Somehow in my copying and pasting I lost the "=". Yes, if your test is <=0 then you should be okay.

 

[PanzerAttack]

You're a gent.