Using IIF and 'And' statement in Form

[greensleeves149]

Hello all,

I've been working on an IIF statement on one of the controls on my form and for over 4 days I can't seem to figure it out- so I thought it's time to reach out for some fresh minds and ideas.

Happy to provide more information if required, but I didn't want to bored people with TMI!

Basically I have an IIF statement that uses other fields on the form to return a certain result. When it wasn't working as expected, I've broken down the IIF statement to try and find out where it's falling over and from what I can see, it's not working correctly if I add an 'And' to the IIF statement.

I've tested taking the AND statement out of the formula and if I use either one or the other components, the formula works as expected (so the naming conventions I assume are all correct). As soon as I add the AND back though, the result on the form will always show the 'false' component ("More") when at times it should show the 'true' ("Less")- depending on the other fields on the form off course. There are no error messages showing up either.

Here's the IIF with the And statement:

=IIf(([MinimumRatePurchaseLimit]<>0) And ([TotalOrderAmt]<[MinimumRatePurchaseLimit]),"Less","More")

Could someone please shed some light on this?

Thanking you.

 

[theDBguy]

The expression syntax looks correct. Without seeing the data, it's hard to say why you're getting unexpected results. Are all the fields involved numeric?

 

[June7]

Could either field be Null?

 

[greensleeves149]

If the syntax looks correct to you guys, then it seems like there's something I'm missing with the data types that's possibly the problem- so I'll attach a small sample of the database as I see without it, it would be difficult to answer.

@theDBguy: The fields are currency/numerical based.

@June7: The null question is interesting...

The way this form works, is that a user selects a Category at the top of the form and also enters a sales price. Upon selecting a category, you will see some of the other fields on the form change accordingly. Only a few categories have a "Minimum Purchase Rate" value more than $0, otherwise the value in that field should be $0.

So where a category does have a value more than $0 (take 'Beauty' for example which will show $10), then depending on the entered Purchase Price, the IIF statement should adjust accordingly. Hopefully that makes sense.

Thank you.

 

[arnelgp]

use Nz() function.
your TotalOrderAmt is Unbound and has no Default value (thus it is Null).

=IIf(([MinimumRatePurchaseLimit]<>0) And (Nz([TotalOrderAmt], 0) < [MinimumRatePurchaseLimit]),"Less","More")

 

[greensleeves149]

use Nz() function.
your TotalOrderAmt is Unbound and has no Default value (thus it is Null).

=IIf(([MinimumRatePurchaseLimit]<>0) And (Nz([TotalOrderAmt], 0) < [MinimumRatePurchaseLimit]),"Less","More")

Thanks so much for that. I've never used the Nz function.