My long IIF statement is only working the first 2 not all of them

[cec]

i have a long iif statement in a query that will only populate the frist 2 iif statements:\



Expr5: IIf([Expr1]="","",IIf([Expr1]>"00hrs 00min"<="12hrs 00min","< Days",IIf([Expr1]>"12hrs 01min"<="24hrs 00min","2 Days",IIf([Expr1]>"24hrs 01min"<="36hrs 00min","3 Days",IIf([Expr1]>"36hrs 01min"<="48hrs 00min","4 Days",IIf([Expr1]>"48hrs 01min",">5 Days")))))

Expr1:42hrs 37min

the iif statement gives me "< Days" and actually it gives that one to me for all of them.

can someone help me with this?

[pbaldy]

Well, for 2 tests you have to repeat the field, like:

IIf([Expr1]>"00hrs 00min" And [Expr1] <="12hrs 00min"...

That said, you can rely on the fact that Access will stop at the first True result so you can simply test for <12, <24, etc. You might find the Switch() function easier to work with than the nested IIf() functions.

[cec]

thanks pbaldy that did the trick now it's just not reading right, giving me the wrong expression.

Expr5: IIf([Expr1]="","",IIf([Expr1]>"0hrs 0min" And [Expr1]<="12hrs 0min","< Days",IIf([Expr1]>"12hrs 0min" And [Expr1]<="24hrs 0min","2 Days",IIf([Expr1]>"24hrs 0min" And [Expr1]<="36hrs 0min","3 Days",IIf([Expr1]>"36hrs 0min" And [Expr1]<="48hrs 0min","4 Days",IIf([Expr1]>"48hrs 0min",">5 Days"))))))

I have one that reads "5hrs 5min" and it returns ">5 Days"

Some are populating right and some are not....would you know why it's doing this?

[pbaldy]

What is the data type of Expr1, the poorly named field? I assume it's text, since you have the values in quotes. You're getting an alphabetic comparison instead of a numeric one, so 5 hours is greater than 48 hours because 5 is greater than 4. To do comparisons like you're doing, Expr1 should be a date/time or numeric value.

[ssanfu]

Disregarding the data type of Expr1, the poorly named field, if you restructure the IIF() function, it can be shortened considerably.

So here are your conditions:

if          then
-------------------
>48 hrs  > 5 Days

>36 hrs    4 Days

>24 hrs    3 Days

>12 hrs    2 Days

> 0 hrs   < 1 day

If you structure the logic (conditions) to "fall through" from largest to smallest, you have fewer checks to make and the statement is simpler. For example, lets say that Expr1 = "24hrs 40mins".

check...
is Expr1 = "" No, then continue
is Expr1 = "> 48hrs 0mins" No, continue
is Expr1 = "> 36hrs 0mins" No, continue
is Expr1 = "> 24hrs 0mins" Yes, return "3 Days" and exit function
no need to check the remaining tests.


The IIF() statement would be: (I think I got the tests in the correct order )

Expr5: IIf([Expr1]="","",IIf([Expr1]>"48hrs 0min","> 5 Days",IIf([Expr1]>"36hrs 0min","4 Days",IIf([Expr1]>"24hrs 0min","3 Days",IIf([Expr1]>"12hrs 0min","2 Days","< 1 Day"))))))

Of course, the field type needs to be corrected......

Just something to think about. Or maybe I'm just bent a little strange....

[pbaldy]

That's the long version of what I mentioned in post 2. The Switch() function would be shorter still and easier to work with than nested IIf() functions.