Multiply form field by separate table field.

[CKENT]

Ok I have been customizing this DB for a long time. Basically it is an invoice form, so... I have the tax rates hard coded for calculation. My business is in three states, so I have three different tax rates. Here is my VBA code for this section.

If Me!StateIn = "TN" Or Me!ShiptoState = "TN" Then
    Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.0975
    ElseIf Me!StateIn = "FL" Or Me!ShiptoState = "FL" Then
    Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.06
    ElseIf Me!StateIn = "NC" Or Me!ShiptoState = "NC" Then
    Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.0825
    Else: Me!Tax = "0.00"
    End If

Now because our tax rates keep changing constantly this is getting to be a pain to update. I have added a new table called "tax" with two fields named "taxstate" which is the Primary key and "taxrate" which is a decimal field. Three records in that table are as follows,
taxstate - taxrate
TN - 0.0975
FL - 0.06
NC - 0.0825

What I am trying to do is change the hard coded tax rate in the code block above so that it pulls the "taxrate" from the new "tax" table if the "taxstate" equals the respective state.

Any help on this will be greatly appreciated

[CKENT]

Anybody have any ideas on this???

[TheSmileyCoder]

You use a domain lookup function.

Me.TaxRate=DLookUp("taxrate","tax","taxstate=" & me.StateIn)

You can enclose the DLookUp in a Nz function (in case no result is returned, I.E. if Me.StateIn is not one of your 3 states. In that case it would look like:

Me.TaxRate=Nz(DLookUp("taxrate","tax","taxstate=" & me.StateIn),0)

[nico5038]

Bit puzzled, what to do when StateIn differs from ShiptoState ?
Now TN will 'overwrite', even when another 'lower taxed' state is involved. When you would need always the highest tax, then the FL should have been the last test....

Nic;o)

[CKENT]

Ok I am a little confused, and sorry for sounding ignorant but... How would I use that in the VBA code noted above.

The tax table is a completely separate table from the form/table I am using, and it does not have a TaxRate field on the form. It has a "tax" field but it has to show the tax as calculated above. So that it actually shows the tax amount rather than the tax rate.

Nico I see what you are saying, the code that smiley gave would not work for that. Sometimes, we get things in from one state and have to ship to another. The code I posted above does work unless for example I get something in from a taxable state and ship it to a non-tax state then I have to manually clear out the tax.

[TheSmileyCoder]

I don't know anything about taxes between countries. How do you decide what tax to apply? I thought it would always be based on the shipping TO location.
Ill post some more code later for you.

[TheSmileyCoder]

Here you go:

Public Sub SetTax()

'Find taxrate
    Dim dbTaxRate As Double
    Dim strTaxState As String
    If Me!StateIn = "TN" Or Me!ShiptoState = "TN" Then
        strTaxState = "TN"
    ElseIf Me!StateIn = "FL" Or Me!ShiptoState = "FL" Then
        strTaxState = "FL"
    ElseIf Me!StateIn = "NC" Or Me!ShiptoState = "NC" Then
        strTaxState = "NC"
    Else
        strTaxState = ""
    End If
    
    
    'Lookup taxrate from table
    dbTaxRate = Nz(DLookup("taxrate", "tax", "taxstate='" & strTaxState & "'"), 0)

'Set Tax
    Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * dbTaxRate


End Sub

Let me just take the chance to give some advice on variable/field/table naming.
When your looking at the domain lookup function (DLookup), it can be hard to "guess/remember" what its doing with those field/table names. Try to imagine instead naming the table of taxrates "tbl_TaxRat es", and the field within the table:
tx_State, Textfield, Primary Key,
db_TaxRate, Number-Double.
The tx indicating its a textfield, the db indicating its a double field.

Now try looking at the same dlookup function call:

dbTaxRate = Nz(DLookup("db_TaxRate", "tbl_TaxRates", "tx_State='" & strTaxState & "'"), 0)

Its immediatly obvous that we are getting a taxrate, represented by a double, from a table "tbl_TaxRat es.

You might ask why I have a underscore in one of the db_TaxRate / dbTaxRate, well thats because if its a Fieldname in a table, I use the underscore db_TaxRate, and if its a variable in my code I go without the underscore dbTaxRate.

Why do I call its tx_State in 1 place, and strTaxState in another place? Again because the underscore tells me its a field in a table, the tx tells me its a field of type Text. A Text field can max hold 255 charecters, possibly less depending on what you have specified in the table design. the strTaxState tells me its a string, and a string can hold way more then 255 chars (Im actually not sure what the limit is on a string, I have not encountered any limit yet).

But when I look at code saying Me.tx_State=str TaxState for instance, I would immediatly know that I should be sure that the textfield can hold the size of the variable in strTaxState.

[CKENT]

Thank you a bunch, with a little editing it worked perfectly. I had to rethink some of my practices with my database, so that tax is based only off of the ship to field. Otherwise it caused problems and too many if statements.

Once again thank you, your help is greatly appreciated.

[TheSmileyCoder]

Your welcome. Glad I was able to help you.