By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Multiply form field by separate table field.

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. If Me!StateIn = "TN" Or Me!ShiptoState = "TN" Then
  2.     Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.0975
  3.     ElseIf Me!StateIn = "FL" Or Me!ShiptoState = "FL" Then
  4.     Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.06
  5.     ElseIf Me!StateIn = "NC" Or Me!ShiptoState = "NC" Then
  6.     Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * 0.0825
  7.     Else: Me!Tax = "0.00"
  8.     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
Jan 15 '10 #1
Share this Question
Share on Google+
8 Replies


P: 4
Anybody have any ideas on this???
Jan 16 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
You use a domain lookup function.

Expand|Select|Wrap|Line Numbers
  1. Me.TaxRate=DLookUp("taxrate","tax","taxstate=" & me.StateIn)
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Me.TaxRate=Nz(DLookUp("taxrate","tax","taxstate=" & me.StateIn),0)
  2.  
Jan 16 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 16 '10 #4

P: 4
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.
Jan 17 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 17 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Here you go:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SetTax()
  2.  
  3. 'Find taxrate
  4.     Dim dbTaxRate As Double
  5.     Dim strTaxState As String
  6.     If Me!StateIn = "TN" Or Me!ShiptoState = "TN" Then
  7.         strTaxState = "TN"
  8.     ElseIf Me!StateIn = "FL" Or Me!ShiptoState = "FL" Then
  9.         strTaxState = "FL"
  10.     ElseIf Me!StateIn = "NC" Or Me!ShiptoState = "NC" Then
  11.         strTaxState = "NC"
  12.     Else
  13.         strTaxState = ""
  14.     End If
  15.  
  16.  
  17.     'Lookup taxrate from table
  18.     dbTaxRate = Nz(DLookup("taxrate", "tax", "taxstate='" & strTaxState & "'"), 0)
  19.  
  20. 'Set Tax
  21.     Me!Tax = (Me!PartTTL + Postage + LaborTTL - Discount) * dbTaxRate
  22.  
  23.  
  24. 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_TaxRates", 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:
Expand|Select|Wrap|Line Numbers
  1. 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_TaxRates.

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=strTaxState for instance, I would immediatly know that I should be sure that the textfield can hold the size of the variable in strTaxState.
Jan 17 '10 #7

P: 4
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.
Jan 17 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Your welcome. Glad I was able to help you.
Jan 17 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.