IIF calculation

[Sam]

I have a query that is calculating royalty for book and cd sales. My royalty
field is currently =Sales*.2. This gives me the dollar value for the royalty
on sales. I need the royalty field to also do this: IIF 'SKU' =B* then
Sales*.2 or IIF 'SKU =C then Sales*.1.
Thanks!

 

[KARL DEWEY]

Here if SKU other than B* it uses Sales*.1 --
IIF([SKU] Like "B*", Sales*.2, Sales*.1)

 

[Klatuu]

Karl's solution is valid. I would, however, like to introduce a philosophical
concept. Data should not be hard coded into your code. Data should always
be in tables. This avoids issues where data can (rest assured, it will)
change and you have to modify and redistribute your application to comply
with the new requirement. It is much easier just to change the value in a
table. Also, you currently have B and C, at some time you may need to allow
for D. So create a table
tblRoyaltyPct
SKU - Primary key
RltyPct - Numeric Single

For your current situation
SKU RltyPct
B 0.2
C 0.1

In whatever table you are using, you will want a field that will tell you
which SKU to use. This will give you a couple of options. One would be to
include tblRoyaltyPct in your query joined on the SKU field, or you could use
a DLookup to find the value. Including it in the query would be faster.

 

[Sam]

Thanks!!! it works!

Here if SKU other than B* it uses Sales*.1 --
IIF([SKU] Like "B*", Sales*.2, Sales*.1)

--
KARL DEWEY
Build a little - Test a little

I have a query that is calculating royalty for book and cd sales. My royalty
field is currently =Sales*.2. This gives me the dollar value for the royalty
on sales. I need the royalty field to also do this: IIF 'SKU' =B* then
Sales*.2 or IIF 'SKU =C then Sales*.1.
Thanks!