434,654 Members | 1,840 Online
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

# I am trying to change this code

 P: 76 I downloaded a template off of the microsoft website, and I am trying to change this code: =nz([Parts Total])+nz([Labor Total])+CLng((nz([Labor Total])+nz([Parts Total]))*nz([SalesTaxRate])*100)/100 I want to get the sales tax to calculate to the Parts Total, and then add Labor. I do not want to tax the Labor. Any ideas? Thanks, Brian Oct 23 '07 #1
9 Replies

 Expert 2.5K+ P: 3,532 I think you have more problems with your formula than just that it's taxing both parts and labor! It appears that you're charging twice for both parts and labor! How about giving us the data type and so forth for your fields. I assume they're named [Parts Total] [Labor Total] [SalesTaxRate] You should really change your names to elinimate the spaces! PartsTotal instead of Parts Total. This will save you trouble down the road. Linq ;0)> Oct 23 '07 #2

 P: 76 Thanks for replying. That is correct. Now I pulled this formula from the expresion builder. It calculates corectly, if Labor is supposed to be taxed, but I can't tax Labor where I live. I don't know how the formula works, but I just need something that does work. Labor, Parts Total, and SalesTaxRate, all come from a diffrent form. Do you need any other informaton? Thanks, Brian Oct 23 '07 #3

 Expert 2.5K+ P: 3,532 Assuming PartsTotal and LaborTotal are both datatype Currency (which they should be) and SalesTax Rate is Single, these should work: If the SalesTax Rate is being entered as 5 for 5% Expand|Select|Wrap|Line Numbers Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)   If the SalesTax Rate is being entered as .05 for 5% Expand|Select|Wrap|Line Numbers Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) * 100 / 100)  I've tested both of these very quickly and believe they're right. Let me know if you have problems. Linq ;0)> Oct 23 '07 #4

 P: 76 I replace the one I had with the one you gave me, but it is not working. Wher am I supposed to put the code at? Oct 23 '07 #5

 Expert 2.5K+ P: 3,532 There are a number ways to do this but I'd do it by placing the formula in code in the AfterUpdate event for each of the 3 textboxes holding PartsTotal, LaborTotal and SalesTaxRate. That way, the TotalBill will be adjusted anytime you change any of the 3 components. Since TotalBill is a calculated field, it needs to be re-calculated each time you access the record, hence the code in the Form_Current event. Expand|Select|Wrap|Line Numbers Private Sub Form_Current() Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)  End Sub   Private Sub LaborTotal_AfterUpdate() Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)  End Sub   Private Sub PartsTotal_AfterUpdate() Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)  End Sub   Private Sub SalesTaxRate_AfterUpdate() Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)  End Sub   Notice that I used the version of the formula where you enter 5 for 5%; adjust it to the other formula if users are entering .05 for 5%. Linq ;0)> Oct 23 '07 #6

 P: 76 One more question, I think. The form that holds the end result, the text box had the original formula I gave you, as the control source. Do I need to put anything there now? Oct 23 '07 #7

 P: 76 I figured it out, All I had to do was delete a part of the equation. Like this: =nz([Parts Total])+nz([Labor Total])+nz([Parts Total])*nz([SalesTaxRate])*100/100 So I have it working now. Thanks for your help, Brian Oct 24 '07 #8

 Expert 2.5K+ P: 3,532 Glad you got it working! Sorry I didn't get back to you sooner! After four months of no rain, we got 2 inches Tuesday nite/Wednesday morning and my ISP went down for almost 24 hours! Linq ;0)> Oct 26 '07 #9

 P: 76 Thats ok, Talk to you later. Brian Oct 26 '07 #10