IIF calculation

  • Thread starter Thread starter Sam
  • Start date Start date
S

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!
 
Here if SKU other than B* it uses Sales*.1 --
IIF([SKU] Like "B*", Sales*.2, Sales*.1)
 
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.
 
Thanks!!! it works! :)

KARL DEWEY said:
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


Sam said:
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!
Click to expand...
Click to expand...
 
Back
Top