Variable Tax Rate (XP)

**CharliesDaughter** · 2004-03-12 01:12

While working on some changes for the funeral home database, I have come across the following problem:

The following is the expression I use to determine the tax rate:

RateofTax: IIf([DateofDeath]<#7/1/2003#,0.07,0.08)

So if Date of Deather is before 7/1/2003, the tax rate is 7%; otherwise 8%.

HOWEVER! If a funeral is prepaid, the funds are not received/booked until the person dies (they are held in a trust), but the fact that they may have actually been paid before 7/1/2003 means we have to calculate the tax at the earlier (7%) rate.

We have a Yes/No checkbox on the data entry screen that is checked if it is a prepaid funeral/unchecked if it is not, and I'd like to tie the formula into that check box.

I will continue to try this as I wait but wonder if anyone can help.

Thanks.

**HansV** · 2004-03-12 01:18

If the check box is bound to a field named PrePaid:

RateofTax: IIf([PrePaid] = True Or [DateOfDeath] < #7/1/2003#, 0.07, 0.08)

If the situation becomes more complicated (several date ranges with differerent rates, more exceptions), it might be better maintainable if you wrote a custom function to calculate the tax rate.

**CharliesDaughter** · 2004-03-12 01:30

What makes it complicated, HansV, is that it still has to be tied into the original formula , i.e.

RateofTax: IIf([DateofDeath]<#7/1/2003#,0.07,0.08) because if it is NOT prepaid, then the forgoing is the way it has to be calculated, if not, then what you sent would work. So, I guess it's kind of an either/or situation. In any case, those formulas must be tied together.

Thanks.

**HansV** · 2004-03-12 01:38

This is what the expression I posted would do, if I am not mistaken:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td align=center>PrePaid</td><td align=center>DateofDeath<7/1/2003</td><td align=center>A Or B</td><td align=center>Tax Rate</td><td align=center>2</td><td align=center>TRUE</td><td align=center>TRUE</td><td align=center>TRUE</td><td align=center>0.07</td><td align=center>3</td><td align=center>TRUE</td><td align=center>FALSE</td><td align=center>TRUE</td><td align=center>0.07</td><td align=center>4</td><td align=center>FALSE</td><td align=center>TRUE</td><td align=center>TRUE</td><td align=center>0.07</td><td align=center>5</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>0.08</td></table>
If that is not what you intended, how should the tax rate be calculated?

**CharliesDaughter** · 2004-03-12 01:54

Actually for prepaid funerals, what I'm finding is pivotal is WHEN the prepayment was made. Therefore,

If Prepaid before 7/1/03, tax is 7%
If Prepaid after 7/1/03, tax is 8%
If not Prepaid and date of death is before 7/1/03, tax is 7%
If not Prepaid and date of death is after 7/1/03, tax is 8%

This is a little different than what I first described. Using your example, the item on Line 3 needs to return 8%.

Does that explain it a little better? I hope so.

**HansV** · 2004-03-12 02:03

Try this one:

RateOfTax: IIf([Prepaid] = True And [DateOfPrePaid] < # 7/1/2003# Or [DateOfDeath] < #7/1/2003#, 0.07, 0.08)

**CharliesDaughter** · 2004-03-12 02:13

I think had I put OR in my expression rather than a comma, it would have worked. I believe that is going to work fine, but I have to do some testing and will let you know.

Once again, many thanks for the help. Sadly, I have gotten away from doing a lot of this over the past year but I have to maintain this thing for the people I wrote it for and because I now spend my days in a one-person law office, I'm also too exhausted to do anything else when I get home. I did give it a good old college try and I was close but you seem to have saved the day again.

Thanks much!

**CharliesDaughter** · 2004-03-23 01:52

To make this all just a little more confusing - and fun - we found after making the changes discussed above, that the tax rates changed on old records. Not such a problem normally except that the old records are showing up on the Accounts Receivable report (for the amount of the difference in taxes!).

So, what I am attempting to do now is something like the following:

RateofTax: IIf([Prepaid]=Yes And [Date Prepaid]BETWEEN #9/1/1969# and #9/30/1975# Or [DateofDeath] between #9/1/1969 and #9/30/1975#, - the tax rate is 0.055)

RateofTax: IIf([Prepaid]=Yes And [Date Prepaid]BETWEEN #10/1/1975# and #9/30/1987# Or [DateofDeath] between #10/1/1975 and #9/30/1987#, the tax rate is 0.065)

RateofTax: IIf([Prepaid]=Yes And [Date Prepaid]BETWEEN #10/1/1987# and #6/30/2003# Or [DateofDeath] between #10/1/1987 and #6/30/2003#, the tax rate is 0.07

OR [Date Pepaid]>7/1/2003, or [DateofDeath]>7/1/2003, the tax rate is.08)

The above is the coding I've used, except that at the end of each example, I've put a note showing what the tax rate should be for that period of time. There are in fact some records older than 1969 but I'm trying to talk the into pulling those (and even some of these) out of the main table. It's an uphill battle with these folks, though, so for now I have to try to deal with this.

I have been working on this for two days now and haven't come up with anything that works consistently yet, so I decided to put it out here for whatever help anyone can give me.

Thanks

**HansV** · 2004-03-23 07:38

In the following I have assumed that you want to include records from before 9/1/69 and that the tax rate for those was 0.045:

RateOfTax: IIf([Prepaid] And [Date Prepaid]<#9/1/1969# Or [DateofDeath]<#9/1/1969#,0.045,IIf([Prepaid] And [Date Prepaid] Between #9/1/1969# And #9/30/1975# Or [DateofDeath] Between #9/1/1969# And #9/30/1975#,0.055,IIf([Prepaid] And [Date Prepaid] Between #10/1/1975# And #9/30/1987# Or [DateofDeath] Between #10/1/1975# And #9/30/1986#,0.065,IIf([Prepaid] And [Date Prepaid] Between #10/1/1987# And #6/30/2003# Or [DateofDeath] Between #10/1/1987# And #6/30/2003#,0.07,0.08))))

If you remove records from before 9/1/69, the expression becomes slightly shorter:

RateOfTax: IIf([Prepaid] And [Date Prepaid] Between #9/1/1969# And #9/30/1975# Or [DateofDeath] Between #9/1/1969# And #9/30/1975#,0.055,IIf([Prepaid] And [Date Prepaid] Between #10/1/1975# And #9/30/1987# Or [DateofDeath] Between #10/1/1975# And #9/30/1986#,0.065,IIf([Prepaid] And [Date Prepaid] Between #10/1/1987# And #6/30/2003# Or [DateofDeath] Between #10/1/1987# And #6/30/2003#,0.07,0.08)))

**CharliesDaughter** · 2004-03-31 00:27

Worked just fine and I thank you for the assistance. The next problem with this thing is going to be a real bear and again affects the tax rates. I will continue to struggle with it for days yet to see if I can find the solution on my own. I have no doubt I'll be back here at some point looking for pointers.

Thanks again

**MarkLiquorman** · 2004-03-31 12:06

Is the TaxRate stored with the Client record? Or do you always just calculate it when needed?

**CharliesDaughter** · 2004-04-01 00:37

Let's see if I can explain this. First, the tax rate is stored with the client record. A little history. I developed this database for a local funeral service; the owner is a friend and nothing he could find commercially did the job for him. It is a one-to-one relationship as (obviously) you only die once (no pun intended!). I started out with 50 years of records, as his father started the business and they kept all this stuff - and I could not convince him not to put everything in the database. Every six months we talk about what his needs are and have been able to set this thing up so that it prints his data on blank death certificate, social security forms, etc., and they love it!

Now, to tax. The problem is that the State of Ohio only allows a funeral service to charge sales tax on 50% of some items - casket, services and special items for example. So we have to add up those items, divide by two, add in the rest of the items and the apply a tax rate.

The tax rate has been 7% since the database was developed, but on 7/1/3, the sales tax rate went to 8%, so we had to fix it so that <7/1/03 tax rate was 7%, otherwise 8%. Of course, that then changed all the old records - regardless of whether they were really subject to 7%. We could have ignored it except the difference in tax rate caused some records to erroneously appear on his Accounts Receivable Report. I then contacted the Dept. of Taxation and got the rates going back to 1960, and applied those rates, with the help of Hans V, so that the tax rate would be correct depending upon the date of death for each record. To complicate all this - and what I didn't know until all the foregoing was done - is the fact that there are "pre-paid" funerals; pre-paid mostly meaning a casket and services are paid for, and you must apply the correct tax rate for the date the funeral was prepaid (not the date of death as in other records). So, fine, got that done.

However (and there's always a however with this), now he tells me that if the family orders something else (flowers, for example) after the person dies, those items must be at the currect tax rate and everything else at the tax rate in existence when the funeral was prepaid.

Hans V came up with the following (before I knew about this last item) and it works fine. So, now I am working to come up with what I need to handle items purchased after date of death for a pre-paid funeral.

RateOfTax: IIf([Prepaid] And [Date Prepaid]<#9/1/1969# Or [DateofDeath]<#9/1/1969#,0.045,IIf([Prepaid] And [Date Prepaid] Between #9/1/1969# And #9/30/1975# Or [DateofDeath] Between #9/1/1969# And #9/30/1975#,0.055,IIf([Prepaid] And [Date Prepaid] Between #10/1/1975# And #9/30/1987# Or [DateofDeath] Between #10/1/1975# And #9/30/1986#,0.065,IIf([Prepaid] And [Date Prepaid] Between #10/1/1987# And #6/30/2003# Or [DateofDeath] Between #10/1/1987# And #6/30/2003#,0.07,0.08))))

Long answer to your question and probably more than you wanted to know, but that's the whole story.

Thanks

CD

**MarkLiquorman** · 2004-04-01 00:57

Probably a little more than I need to now!<g>

I was just curious as to when you were calculating that TaxRate. I see nothing wrong with storing the calculated rate in the record, as you are just recording the sales tax in effect at a point in time, and calculating it in the form. Obviously, anyone dying now would get the 8% rate anyway; or at least the current rate at that point in time, given that the rate could be raised at any time in the future.

Building on that premise, I'd think it would be useful to just update this rate in the older records. Rather than having to do it all in 1 huge update query, it could be done in a couple of passes. This would be a 1-time deal, as you'd never have to do it again.

**CharliesDaughter** · 2004-06-06 21:48

Well, it's me again! The funeral home database has been as bad as a bad soap opera lately but I have done a lot of decent work and it's up and running well for now. However, here's a new twist that I have struggled with for three weeks now:

We presently calculate tax as follows in the query:

Tax: (([Casket]+[TotalSectionA]+[TotalSectionC])/2)*[RateofTax]+([TotalSectionB]-[Casket])*[RateofTax]

For information:
A=Services
B=Merchandise
C=Special Charges

The Funeral Home does

**HansV** · 2004-06-07 07:02

It is probably best to calculate the amount of tax for each item separately, taking prepaid or not and the date into account, and then sum the tax amounts, instead of lumping the items together and then calculating the tax. By doing separate calculations, you can apply different rates to different items in the same section, depending on the date.

Thread: Variable Tax Rate (XP)

Thread Tools