Calculation Errors

[billgyrotech]

I have 2 errors:



On Workorder 2 the amount due is 0.01 cents. I don't know why this is.

I can't figure out how to calculate sales tax on parts only.

Could someone help please.

Thank you,
billgyrotech

[June7]

You get (-.01) because the calculation is using the full unrounded value of the terms. Setting a format, such as Currency, is a display setting - it does not change the actual value. So the values are: 578.055 - 578.06 which results in (-.005) and because of the Currency display setting, rounds to (-.01). Use Round or Format function in the calcs. Be aware, Round in Access uses an even/odd rule. When the digit after the round place is 5:
Round(578.045,2) will round to 578.04
Round(578.065,2) will round to 578.06
Round(578.055,2) will round to 578.06

Format function does not follow the even/odd rule but the result is a text string, not a number, and that creates an issue in Access if you try to add two terms resulting from Format function. The + is a valid concatenation operator (holdover from old Basic language) and the terms will concatenate, not sum. Convert at least one of the formatted strings back to a number.
Format(578.045,"0.00") + Val(Format(578.055,"0.00")) will be 578.05 + 578.06 = 1156.11
Other operations (* - /) do not have this issue with the Format function.

If you don't want sales tax on labor, don't include labor in that term.
=Nz([Parts Total])*0.0825+Nz([Parts Total])+Nz([Labor Total])

[billgyrotech]

The calculations worked great! Thank you for that!

Could you check one more thing for me please? On the payment button:

Select and try to enter a payment (when the password prompt pops up just press enter because the password is blank for now). When trying to enter a payment, an error message pops up: can't find 'Amount Due'. I cannot trace where this is coming from.

Thank you,
billgyrotech

[June7]

I don't get that error message from the version I downloaded.

What have you changed since posting the file?

[billgyrotech]

It only happens when trying to add a payment or select payment method.

[June7]

Okay, selecting payment type triggers that error. The BeforeUpdate event of the payment type combobox refers to control on Workorder by Customer subform. The code is looking for Amount Due but the control is named Text19. Fix the name.

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names.

[billgyrotech]

Thanks, that worked. I know that I have used spaces for names. Why is this a problem?

[June7]

If you use spaces... - need to remember to enclose in []. Access will try to include the [] but can be confused by these features. VBA will never provide [] for you. So if you name a table, query, form, report, field, control with these features and refer to it in VBA must remember to type the []. Without these features the [] not needed.

Also, don't use reserved words (such as Date) as a complete field name, again need [] if you do. Use like DateEnter, DatePaid.

[billgyrotech]

Okay. Is there a way to find all names and replace without getting errors?

[June7]

You mean rename tables, forms, reports, queries, controls, fields throughout the project automatically? Need a third-party addon for that. I use Rick Fisher's Find & Replace. Cost about $50. Still have to input the search and replace string for each case you want to fix.

Or, you can live with what you have designed and note for future - Remember the [].

[billgyrotech]

I think I will live with what I have for now and follow that rule in the future.

Thank you very much for your help and have a great day!
billgyrotech