IIF, IIF, IIF, IIF

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I have a query to calculate commissions that has multiple
IIF's. It's going to have many more IIF's and I was
wondering if there was a way to use a word processor or
better yet a spreadsheet where I can use separate lines
and indents to build the query and then copy/paste it into
Access?

Are there standard conventions for breaking lines and/or
indenting lines? (i.e. if the answer to an IIF is false
will it look to the next indented line or if true will it
look to the next line that begins at the same space as the
IIF begins)



Here's a piece of it:


comm_zb: IIf([desc3]=9,0,IIf([quantity] Mod 100<>0,0,IIf
([gross]>=2500,(+[gross]*0.009)+22,IIf(+[gross]>=800,(+
[gross]*0.013)+12,IIf(+[gross]>=100,(+[gross]*0.013)
+6.4,0)))))
 
Place your cursor in the box and press Shift+F2. You can paste into here
using Ctrl+V, but it will all need to be on one line (it'll wrap in the
window, but the carraige returns need to be removed). Another option is to
go to SQL view of the query and paste it there, but once again, remove the
carraige returns from the equation before you actually try to run the query.
SQL view will accept carraige returns in the SQL statement itself, and may
accept them in the equation (I've tried a couple and it worked), but I don't
know if it will work all the time.
 
I would never create an expression this complex and difficult to maintain.

My first attempt would be to use data values in tables that describe the
ranges and multipliers. This would keep all your business rules dynamic and
maintenance would be done in data rather than complex expressions.

My next alternative would be to create a user defined function with Select
Case type statements that would be much easier to maintain and could be used
anywhere in your application.
 
Back
Top