Form Calculation is wrong!

[Lou_Reed]

In performing the tutorial for Lesson 21, I was working on the College Park Auto Repair Form.




I got the form as shown in the tutorial at about halfway through.

However, I did not get anything for Tax Amount and Total Order. I believe that they

are related. I cannot get the Total Order - it is calculated in the sum: if the Tax Amount is wrong so is Total Order.

I got no errors when calculating the tax amount, but it certainly gave me useless information in the text box:

#Name?

.
What did I do wrong? All the other calculations for that form are correct (except for Total Order).


Any help appreciated. Thanks in advance.


Respectfully,


Lou_Reed


The Lesson 21 tutorial is here:

http://www.functionx.com/access/Lesson21.htm

[orange]

Can you post a copy of your database?

[June7]

To know what you might have done wrong, we need to see what you did. As orange suggests, provide copy of db for analysis.

Where in that lesson does it describe these calculations?

[Lou_Reed]

I think that I see the problem. Let me try and explain. I will reproduce the instructions on this post (if I can).

Click the Order Summary tab
Click the Tax Amount text box
In the Property Sheet, click Control Source and change it as follows: 
=Nz([SubTotal1])+Nz([SubTotal2])+Nz([SubTotal3])+Nz([SubTotal4])+Nz([SubTotal5])
On the form, click the Total Labor text box
In the Data tab of the Property Sheet, change the expression in the Control Source as follows: 
=Nz([JobPrice1])+Nz([JobPrice2])+Nz([JobPrice3])+Nz([JobPrice4])+Nz([JobPrice5])
On the form, click the Tax Amount text box
In the Data tab of the Property Sheet, change the expression in the Control Source as follows:
=CLng(CCur(Nz([txtTotalParts])+Nz([txtTotalLabor]))*CDbl(Nz([TaxRate]))*100)/100
On the form, click the unbound Total Order text box
In the Property Sheet, click Control Source and type:
=CCur(Nz([txtTotalParts])) +CCur( Nz([txtTotalLabor])) + CCur(Nz([txtTaxAmount])
Click Format and select Currency

At point 3 and again at point 7. I calculate the Total tax amount. This seems to be calculated twice. Why?

It seems to be wrong.


Respectfully,


Lou_Reed

[Lou_Reed]

Here is my compacted database.


College Park Auto Repair.accdb

Okay, I think that I uploaded it correctly.

Respectfully,

Lou_Reed

[June7]

Steps 3 and 7 are not the same calcs.

Why are there 5 unit price and 5 quantity and 5 job price fields and therefore 5 sub total calcs? This is not non-normalized data structure. You should abandon this tutorial site. Or at least get an understanding of data normalization and recognize the difference that this site is demonstrating and its drawbacks.

You are setting Caption property in table and using the same caption across multiple fields. I NEVER set display properties (such as Format, Caption, InputMask) in table.

The controls on form are not given informative names. Which is supposed to be 'Tax Amount' textbox? There are no calculations in any of these boxes and none are bound to fields.

[orange]

I looked at the database structure from the site --- it is not normalized???? Very surprised!!!
FunctionX has lots of good info -- I can't understand why the table is not normalized. I suppose it's possible they could be focusing on vba etc, but it's so obvious????

[Lou_Reed]

Is that why the calcs on the form for College Park Auto Repair are wrong? The db is not normalized.

Respectfully,

Lou_Reed

[June7]

No.

Review my previous post again. Can't analyze your calcs because there aren't any.

[Lou_Reed]

First you say that the database is wrong. I ask is that why I am getting errors. You say no - read earlier posts.

Which is it. The calculation that are shown appear to be correct except for Total Tax Amount. Obviously, the are not five subtotals.

I can make all of the unused values zero, but I am not sure it would work.

Elaborate.

Respectfully,


Lou_Reed

[June7]

The database is not normalized but calculations should still be possible as specified in the tutorial. Step 3 shows adding 5 sub totals.

The db you posted does not have any calculations to be analyzed.

[Lou_Reed]

Okay, the SubTotal4 and SubTotal5 are zero. As shown in another tab on the form. I think it is Parts Used.

Every subtotal has a value, it is just that only subtotals 4 and 5 are 0.00. In an addition operation it obviously changes nothing.

Under Jobs Performed there are only two entries out of a possible five. The first two have values and the last three do not. I assumed that they are

zero and in the Parts Used tab. It appears not to show that. That is the first thing that I will check.

I will see how this works out.

Respectfully,


Lou_Reed

[Lou_Reed]

I think that I see the error. There is no equivalent Jobs Performed calculation like there is for Parts Used. They are essentially undefined (the last three). Hence adding them in will create the error.

Okay, how did I get Total labor and Total Parts right?

I think that I see the error. I am not sure if it will solve all of the
error seen, but I will try and see.


Respectfully,

Lou_Reed

[Lou_Reed]

In the control source one normally inputs formulas that are like this:

=Nz([JobPrice1])+Nz([JobPrice2])+Nz([JobPrice3])+Nz([JobPrice4])+Nz([JobPrice5]

However , that is not very descriptive. Is it possible to expand that formula like this:

Nz([txtTotalLabor])=Nz([JobPrice1])+Nz([JobPrice2])+Nz([JobPrice3])+Nz([JobPrice4])+Nz([JobPrice5]


and then only use the left hand side in any future calculations? That value, Nz([txtTotalLabor]), seems a lot more descriptive and, of course, it is far easier to write that
than

=Nz([JobPrice1])+Nz([JobPrice2])+Nz([JobPrice3])+Nz([JobPrice4])+Nz([JobPrice5]

if it is ever used in the future.

Any help appreciated. Thanks in advance.

Respectfully,

Lou_Reed

[June7]

Yes, can reference txtTotalLabor textbox in other expressions on the form.

However, saving this value into table is not advised. Do this calc when needed.