Query expression IIF vs VBA code

[jhoiland]

Hello,

I am writing some pricing optimizations for an online store. What I am trying to do is adjust the sale price based upon a combination of 3 factors...cost, weight, min advertised price (MAP).

I know I could code this in VB/ASP for web...not sure how to do it in an access query. Looking around here it looks as if I might be able to do it through a complex nesting of IIF statements too, but those would look messy.

Here is the logic I am trying to achieve...

If cost <25
  if weight<1

    sellprice=cost*1.3

    if sellprice<MAP

      [sellprice=MAP

    end if

  elseif

    sellprice=cost*1.3+3

    if sellprice<MAP

      sellprice=MAP

    end if

  end if

end if

If cost >=25 and <50
   'if weight...etc etc with maybe 5 cost brackets like this total.

Basically I am trying to build in some extra margin for low value orders that have high shipping costs and to let them reduce margin on big ticket orders.

item cost weight MAP sellprice

1    10   .5     null  13
2    10    2     null  16
3    10    3     40    40
4    40    2     null  XXetc

Thanks in advance,

John

[liimra]

Solution/

You can use something like this

 IIf(cost < 25 And Weight < 1, cost * 1.3, IIf(cost * 1.3 < Nz(MAP, 0), MAP, IIf(cost > 40 And Weight < 1, cost * 1.3, IIf(cost < 25 And Weight > 1, cost * 1.3 + 3, cost * 1.3))))

This expression contains 5 scenarios, I think you can use up to twenty but still there are workarounds if you need more scenarios.

Regards,
Ali

[jhoiland]

does this execute all conditions on down the line regardless?

IIF (condition, true, false) The first one would be true for an item less than $30 and under 1 lb...so would the MAP test under the false condition even be executed?

I saw some other examples using SELECT CASE statements that look like more traditional code to me, but I am still not sure how I would incorporate this code into a query result.

JH

[liimra]

Yes

Yes it does. If you want, post all the different scenarios you want and state field names, and then either me or one of the great contributors will help you get the exact statement and from there you can go. Keep in mind that you can use Switch() too.

Regards,
Ali

[liimra]

Did you work it out?

Regards,
Ali