Need Help--Changing Sales Tax without affecting older records 1

[proflola]

Hello All,

I have an expense tracking database. It's based on a query. I have expressions within the query that calculate the sales tax, sub-total, shipping and total. The sales tax expression first checks to see if a checkbox is checked and then figures the tax. The problem is that Virginia's sales tax increased from .045 to 05. I need to update the amount without changing my old records.

Of course, I could archive my older records to another table and then start this year fresh with the new tax amount. First, I wanted to see what you experts might recommend.

Thanks!
Sandy

 

[67peshawar294]

lola,
How about changing your sales_tax field, or adding a new field to your query;

=iif([yourdatefield] > 12/31/2005,[sub_total]*.05,[sub_total]*.045)

jim

 

[missinglinq]

I believe Jim's idea will work in this case, but what if the changing factor was something that could change every few months? This would become quite cumbersome. This type of situation is the reason I always object to the "sage" advice that you should "never, ever" store a calculated value in a table. There are situations where this would be the simpler approach!

BTW, Sandy, are you old enough to remember when Virgina had no Sales Tax? The first tax was 2%, levied in 1966!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Pampers]

Well,
in my opinion - in this case - you don't have to save the calculated field, but only the taxpercentage.

To do this, introduce a taxrate field in your table. Add the 4,5% taxrate to all the old records. Then you can f.e. set a default value in your table to 5%. Or make a new table with one row, holding the current taxrate and use the DLookup-function in your form to retrieve the percentage.

The advantage of the last method is that a user would be able to modify the taxpercentage himself (of course you have to make a small form to allow him or her to do this), after you career skyrocketed and are not avalable anymore to change the defaultvalue in the table (or form);-)



Pampers

you're never too young to learn

 

[67peshawar294]

inglinq,
I neglected to add about the calc fields in tables. I have found however, the people that ask the questions are the ones that do it, reguardless of advise. I must confess, I myself have one table, with a calc field, in the first database I designed when Access first came out in '92. I have never changed it. In fact, I think I had it in Condor before Access was released.
jim
P.S.
I thought it fun to make a mess of your tek-tip ID.
Miss Inglinq. Ha, ha...
No?

 

[missinglinq]

Cute, xaf294! Actually I've never stored calculated values! But I recognize that there are times when it would be the simplest way to go. Stop and think about; is it easier to store the calculated value, or jump thru the hops you've suggested? Lets face it, storage space is not the same valuable commodity it was ten or twenty years ago!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya proflola . . .

You could create an [blue]DB property[/blue] to hold the current tax rate. The [blue]property stays with the DB and can be changed at will.[/blue] You would use a function to return the value where needed. If the function is [blue]CurTax[/blue], your calculation would look like:

[blue]   [sub_total]* [purple]CurTax()[/purple][/blue]

See Ya! . . . . . .

 

[proflola]

This is a GREAT Forum! Thank you all for your great replies! I like the idea of having a tax rate field and it's so simple, I can't believe I didn't think of it. The other solutions were perfect, also.

Thanks everyone!

Sandy

 

[proflola]

The Aceman1,

I like your solution, but I'm not sure how to create the coding for it. I've done some programming in Visual Basic for Access however, I'm still a novice. Could you walk me through it? It sounds like a way to make this work seemlessly.

Thanks again!

--Sandy

 

[TheAceMan1]

proflola . . .

Just bear in mind that [purple]user defined DB properties is not a catch-all![/purple]

In a [blue]new module[/blue] in the [blue]modules window[/blue], copy/paste the following code:

[blue]Public Function CreateDBProp(prpName As String, prpDatTyp As Long, _
                             prpVal) As Boolean
   'prpDatTyp: dbBoolean, dbByte, dbCurrency, dbDate, dbDecimal, dbDouble,
   '           dbFloat, dbInteger, dbLong, dbMemo, dbSingle, dbText
On Error GoTo GotErr
   Dim db As DAO.Database, prp As Property
   Dim Msg As String, Style As Integer, Title As String
   
   If IsEmpty(GetDBProp(prpName)) Then
      Set db = CurrentDb
      Set prp = db.CreateProperty(prpName, prpDatTyp, prpVal)
      db.Properties.Append prp
      CreateDBProp = True
   Else
      Msg = "The property '" & prpName & "' Already Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Create . . . Property Exists!"
      MsgBox Msg, Style, Title
   End If
   
SeeYa:
   Set prp = Nothing
   Set db = Nothing
   Exit Function
   
GotErr:
   Call PropErrMsg
   Resume SeeYa
      
End Function

Public Function GetDBProp(prpName As String)
   'Always assign GetDBProp to Variant data type.
   'Returns Empty if property not found.
   'Use IsEmpty() to determine if property exists.
   Dim Msg As String, Style As Integer, Title As String

On Error GoTo GotErr
   GetDBProp = CurrentDb.Properties(prpName)
   
GotErr:
   
End Function

Public Function SetDBProp(prpName As String, prpVal) As Boolean

On Error GoTo GotErr
   Dim Msg As String, Style As Integer, Title As String
   
   If IsEmpty(GetDBProp(prpName)) Then
      Msg = "The property '" & prpName & "' Doesn't Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Property Doesn't Exists! . . ."
      MsgBox Msg, Style, Title
   Else
      CurrentDb.Properties(prpName) = prpVal
      SetDBProp = True
   End If
   
SeeYa:
   Exit Function
   
GotErr:
   Call PropErrMsg

End Function

Public Function DelDBProp(prpName As String) As Boolean

On Error GoTo GotErr
   Dim db As DAO.Database, prp As Property
   Dim Msg As String, Style As Integer, Title As String
   
   If IsEmpty(GetDBProp(prpName)) Then
      Msg = "The property '" & prpName & "' Doesn't Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Property Doesn't Exists! . . ."
      MsgBox Msg, Style, Title
   Else
      Set db = CurrentDb
      Set prp = db.Properties(prpName)
      db.Properties.Delete (prp.Name)
      DelDBProp = True
   End If

   
SeeYa:
   Set prp = Nothing
   Set db = Nothing
   Exit Function
   
GotErr:
   Call PropErrMsg
   Resume SeeYa
      
End Function

Public Function CurTax()
   CurTax = GetDBProp("TaxRate")
End Function

Public Sub PropErrMsg()
   Dim Msg As String, Style As Integer, Title As String
   
   Msg = "Error " & Err.Number & ": " & Err.Description
   Style = vbCritical + vbOKOnly
   Title = "System Error! . . ."
   MsgBox Msg, Style, Title

End Sub[/blue]

The function names should be self explanatory. All you need to do is create the property [blue]TaxRate[/blue], by opening any module in the modules window, then opening the immediate window (Ctrl + G), and typing:

[blue]? CreateDBProp("TaxRate", dbCurrency, 0.05)[/blue]

Now that the property exists, [blue]you can use the CurTax function anywhere you like[/blue]. You may want to setup a small form for changing the value on the fly . . .

See Ya! . . . . . .

 

[Pampers]

Hi proflola ,
If you want to use the Dlookup for your taxrate, you put it in the defaultproperty of the taxfield on your form. It will look like:
=DLookUp("[TaxRate]";"[lstTaxRate]";"[TaxRateID] = 1")

Pampers

you're never too young to learn