If, then , else calculation

[socks]

I have to change the sales tax rate in two different databases. The sales tax in NY went up .25%. One company only does business in a single county and I changed the tax rate field from a number (I.E .07) to a computation that said:

"If(invoice date <6/1//2003, .07, .0725"). This seems to work and nes invoices are taxed at 7.25% and the old ones relain at 7%.

But the second company is more complicated and they do business in numerous counties that have different tax rates, so if the tax was 7% it becomes 7.25%, and if the county tax was 8% it has changed to 8.25%. So, for each customer I entered an old and new tax rate, and created a computation field for the "tax rate" charged as "If(invoice date <6/1//2003, old tax rate, new tax rate"). I was hoping this calculation would insert the proper tax rate for each account, and if the invoice was entered prior to June 2003 it would use the old lower sales tax rate, and invoices created on or after 6/1/2003 would use the new higher rate. But, this is not working, no matter what the invoice date it seems to use the higher rate. Can anyone see if I made a mistake.

[danjacoby]

It could be worse -- here in NYC our tax rate is now 8.625%.

I'm concerned about the number of typos in your post, especially the "//" in your date (i.e. 6/1//2003). I wonder if a similar (or other) typo in your actual calculation might be causing the problem.

The other possibility could be based on where the base tax rates for each customer are entered. If there is a relationship there, is it properly defined in the calc?

My suggestion for a long-term solution is to create a "TaxRate" file that contains tax rates not for individual customers, but for counties, then have a "County" field for each customer. Finally, base a relationship on a calculation of County and Date to determine the proper tax rate for each invoice. That way, if (or should I say "when") the tax rate changes again, you can create a third field in the "TaxRate" file and reset the calculation in the Invoice file for the new invoices.

[socks]

Thanks for your suggestion. I don't have any typo's in my calculation, and I am not using a relationship. And, I would agree to have a county field for each customer, then change the county rate would be better. But, for now, do you see any errors why this calculation field is not working "If(invoice date <6/1//2003, old tax rate, new tax rate". I want this field to tax the old invoices at the previous rate, and the new ones at .25% higher.

[McCormick]

The If line you put in quotes does have the typo danjacoby menioted; I assume you just cut-and-pasted it from the first posting, and as you said, you have no typos in the real calc. But since you've basically paraphrased the calc, it's a little harder to see what the mistake might be (for me ... maybe someone else gets it right off). Could you put up the literal, exact calculation?

[Lee Smith]

Case(Invoice date<Date(6, 1, 2003),

"old tax rate", "new tax rate")

Try this one, it works

[socks]

Lee

Your calculation worked. Thank you very much. I only made one change by removing the brackets from "old tax rate" and "new tax rate" because they are number fields, and I didn't want text but the actual tax rate number. Thanks again.

Jeff Norensky

[Lee Smith]

Hi Jeff,

You did the right thing. I didn't catch the fact that these were fields. DUH,

Good Job,

Lee