Figuring out a query to calculate taxable sales

[Christin97]

I am trying to figure out how to write a query that calculates taxable sales

Example: Taxable Sales


$0.00 - $62,501.00 * .0343
$62,501.00 - $208,001.00 * .0257
$208,001.00 - $300,000.00 * .0255

that is the table... I have tried building a query with the formula

=IIf([state tax]<62,501.00,[state tax]*.0343,IIf([state tax]<208,001.00,[state tax]*.0257,IIf([state tax]<300,000.00,[state tax]*.0.255)))

this works for the first calculation but it does not follow through to the second or third IIF..

Please help!

Thanks,
Christina

 

[vr]

Try using between versus <.

 

[tlbroadbent]

Try removing the commas and adding a default value if all the tests fail.

=IIf([Sales]<62501, [Sales]*0.0343, IIf([sales]<208001, [sales]*0.0257, IIf([sales]<300000, [sales]*0.0255, [sales]*0.0250))) As [State Tax]

NOTE: [sales]*0.0250 is my default. You'll need to set properly for rates when [sales]>300000. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Amiel]

&quot;...Try removing the commas and adding a default value if all the tests fail.

=IIf([Sales]<62501, [Sales]*0.0343, IIf([sales]<208001, [sales]*0.0257, IIf([sales]<300000, [sales]*0.0255, [sales]*0.0250))) As [State Tax]...&quot;

Now, gentle persons, take a look at what was written
above.Then GOTO Start.


Start.
If The first expression is true the other statements do not get evaluated.

if LightBulbIsOn Then
GOTO done;
End If

If the third statement is true then the first statement is true.
GOTO Start



done:

...sorry but I needed to have some fun with this.
you will have to take the advice of 'vr' above and/or extend your IIF's to include '>' and '<' in the statement. No hard feelings Terry.

Have fun.
Amiel
amielzz@netscape.net

 

[tlbroadbent]

Amiel said, &quot;If The first expression is true the other statements do not get evaluated.&quot;

Agreed. :-D

Then he states, &quot;If the third statement is true then the first statement is true.&quot;

Not so fast! :-(

Explain how [sales]<62501 (1st statement) is true for all values of [sales]<300000 (3rd statement). What if [sales]=251045. Is the first statement true in this case? No! Is the third statement true? Yes! Which value of sales tax do we want to apply? Why 0.0255, of course and that's just what IIf will do.

Let's restructure the IIf statements.I

If [Sales]<62000 Then
[Sales Tax]=[Sales]*0.0343
ElseIf [Sales]<208001 Then
[Sales Tax]=[Sales]*0.0257
ElseIf [Sales]<300000 Then
[Sales Tax]=[Sales]*0.0255
Else
[Sales Tax]=[Sales]*0.0250

If the 1st statement is true apply rate and exit. If false do 2nd test. If 2nd test true apply rate and exit. And so on. If all IF statements fail, the ELSE applies a default tax rate.

The proposed IIf structure has been tested and performs in exactly the same way as the IF-THEN-ELSE statements. Any questions, gentle persons?

No hard feelings, Amiel. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Amiel]

You seem to have figured out the logic, now try using this logic in a SQL Statement. Further create the same logic using an &quot;IIF&quot; macro....

When doing so, you'll have to aknowledge 'vr's' post as being absolutely correct. It's really simple.

The best that I can suggest to you otherwise if, questions still remain for you concerning the [red]origional question [/red] is; GOTO Start.

Amiel
amielzz@netscape.net

 

[Amiel]

In other languages the order of presidence, or order of evaluation, is a consideration when using nested statements. It's a considertion too in VBA. Though it's almost never mentioned. Here's the reason that 'vr's' post is the correct approach to fixing the originally stated problem...

http://msdn.microsoft.com/library/officedev/off2000/acfctIIfS.htm

So, &quot;... In Visual Basic, the IIF function evaluates both truepart and falsepart, even though it returns only one of them...&quot;.. Thus, 'vr's' post was the correct answer.

I was correct in supporting 'vr's' response, but stated the wrong reason. Specifically describing the behavour of the IIF statement as not evaluating the statement beyond the first 'true' statement.

This is incorrect. Based on the description from the reference.

To use this nested IIF statement in a query I suggest making the truepart and falsepart portions mutually exclusive (by using 'between' or using both '<' and '>' in each statement) guaranteeing that only one IIF statement can ever be true. Since the inner-most statement will in fact always be evaluated.

<cute smiley face with tounge sticking out>
This stuff is fun. Enjoy. Amiel
amielzz@netscape.net

 

[tlbroadbent]

Christina,

Amiel is right. You had the correct logic to start. You only needed a slight syntax correction. You will find that your solution works and &quot;< >&quot; or &quot;between&quot; logic is not needed. You can add &quot;between&quot; logic if you want but it won't improve the result.

Hopefully you'll be confident in your own solution and go with it.

By the way, there is no need to create a macro or a function. The IIf function works in a query.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

Amiel,

You should just try it. Much faster than the several threads. Terry is correct. the original problem / issue is placing the formatted currency values (especially the embeded commas as thousands seperators) within the IIF statement. VB thinks of those as ARGUMENT seperators.

MY only 'nit' to 'pick' with Terry would be the &quot;choice of the default value of tax (where sales >= 300000). Although he DID point out the issue, obviously the original poster is not very conversant w/ VB and the inclusion of the default does follow the trend of the lesser values, I think he could have provided an explination of WHY the value is necessary.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

This stuff is fun. LOL

To quote the document that Amiel referenced.

&quot;In Visual Basic, the IIf function evaluates both truepart and falsepart, even though it returns only one of them. In a Microsoft Access form or report, however, the IIf function evaluates either truepart or falsepart, whichever is appropriate. Therefore, you need not be concerned about the undesirable side effects of evaluating both arguments if you use the IIf function in a calculated control, query expression, or macro.&quot; (emphasis added) ;-)

Therefore, according to Microsoft, the query will handle IIf function properly. My own testing in numerous applications has proven it to me.

MichaelRed,

My first post explained, rather poorly I admit, why the default value was needed. If all three IIf functions evaluate false, a default or fall-through value is required. In addition, Christina's original structure would have contained incorrect syntax after removing the commas.

Thanks for the pointer. I certainly try to be more precise in the future. B-) Terry

http://members.home.net/tlbroadbent/index.htm