By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
9 Replies


missinglinq
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

missinglinq
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
  1. Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100)
  2.  
If the SalesTax Rate is being entered as .05 for 5%
Expand|Select|Wrap|Line Numbers
  1. 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

missinglinq
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
  1. Private Sub Form_Current()
  2. Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100) 
  3. End Sub
  4.  
  5. Private Sub LaborTotal_AfterUpdate()
  6. Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100) 
  7. End Sub
  8.  
  9. Private Sub PartsTotal_AfterUpdate()
  10. Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100) 
  11. End Sub
  12.  
  13. Private Sub SalesTaxRate_AfterUpdate()
  14. Me.TotalBill = Nz(Me.PartsTotal) + Nz(Me.LaborTotal) + ((Nz(Me.PartsTotal) * Nz(Me.SalesTaxRate)) / 100) 
  15. End Sub
  16.  
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

missinglinq
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

Post your reply

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