435,148 Members | 748 Online
Need help? Post your question and get tips & solutions from a community of 435,148 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 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 Jan 15 '10 #1
8 Replies

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

 Expert Mod 100+ P: 2,321 You use a domain lookup function. Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers Me.TaxRate=Nz(DLookUp("taxrate","tax","taxstate=" & me.StateIn),0)   Jan 16 '10 #3

 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

 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

 Expert Mod 100+ P: 2,321 Here you go: Expand|Select|Wrap|Line Numbers 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_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 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