Calculating expression in the form is not transferring to report

[ERogers]

I have 4 calculated fields in my form
IIf [division]=43, [Quantity Ordered]/1000*[Price/M],""
IIf [division]=48, [Quantity Ordered]/1000*[Price/M],""
IIf [division]=5, [Quantity Ordered]/1000*[Price/M],""
IIf [division]=6, [Quantity Ordered]/1000*[Price/M],""
Division, Quantity Ordered and Price/m are all fields you enter into the form. The total appears in the correct field once you've entered the division, quantity and price/m


it works just fine in the form, but when I go back to the log (table), all entries show up except my calculated totals.
I have a query and consequently a report that should show just the division and the totals fields, but since the calculated field doesn't get to the table, my report is incomplete.
how and where do I make this calculation so that it shows up on my daily report?

[JoeM]

Are Division, Quantity Ordered and Price/m both or unbound fields?

If the calculations are on done on the Form, that is where they will exist, no where else.
Calculations are never stored on the table level.
If you want your calculations accessible on both a Form and a Report, you may be able to perform them in a query if the fields you are entering the value into are bound to table fields. As long as that query is the Control Source for both your Form and Report, those calculation values from the query will be available for "dropping" on the Form and Report.

Otherwise, you would probably need to recreate the calculations on your Report as well.

[ERogers]

Those that do the entry like it in the form (it allows them to double check their entries) but the report has to have them as well. So I suppose it would be easiest to duplicate the calculation in the report - yes?
That being said, I assume I do that in the Data tab - Control Source.
do I have to say it like this in expression builder to identify the table the division, quantity and price are stored on?
Right now the control source is Hutchins do I change it to this code? - is there something wrong with this code?

IIf[1-25-13 log]![Division]=43,[1-25-13 log]![Order Quantity]/1000*[1-25-13 log]![Price/m],""

Are Division, Quantity Ordered and Price/m both or unbound fields?

If the calculations are on done on the Form, that is where they will exist, no where else.
Calculations are never stored on the table level.
If you want your calculations accessible on both a Form and a Report, you may be able to perform them in a query if the fields you are entering the value into are bound to table fields. As long as that query is the Control Source for both your Form and Report, those calculation values from the query will be available for "dropping" on the Form and Report.

Otherwise, you would probably need to recreate the calculations on your Report as well.

[JoeM]

Let's take a step back here for a second. Are the fields you are entering data into on your Form bound or unbound?
That is the most important detail, and may determine how to best handle the rest.

[ERogers]

The Quantity, Price/m and Division are bound (the way I understand the term) to the table called 1-25-13 log. by that I mean those fields get entered into the form and populate the table.

[JoeM]

That's good that they are bound to a table.
Now what is the Control Source of your Form and Report?
Is it the Table, or a Query based on the table?
What you can do is to create a Query based on the Table, and peform the calculations there in Calculated fields. Those expressions (Calculated fields) are dynamic, so anytime data is added/changed/deleted, the calculation will change.
Now, if you have these calculations in your query, you can use the query as the Control Source of your Form and Report. That way you won't need to create them a second time, because they actually exist in the underlying query. And if you ever need to change your calculation, you only need to change it in one place.

Its make no difference to the end user. They cannot tell if the calculation is being done in the query or in the Form or Report, it will all look the same to them.

[ERogers]

The form is dependent on a query so yes I could do the expression in the query, but either I'm missing something or just plain doing it wrong. in the Field space for each column of the query is the named field from the table. Do I change the Field?
=IIf[1-25-13 log]![Division]=43,[1-25-13 log]![Order Quantity]/1000*[1-25-13 log]![Price/M],""

I tried that, but when I go to the report, (I have it set to ask for beginning date and end date) it now asks me for each field as well as the beginning and ending dates- I don't want that so I think I've got something wrong in the Field?

[ERogers]

Now it says that the expression is either typed incorrectly or is too complicated.

[JoeM]

See this: http://www.dummies.com/how-to/conten...cess-2007.html

[ERogers]

AAAAHHHH! I finally got it to work! Thanks for the help (and letting me talk my way through this) I missed the colon (among other things)
Here's what finally worked for me:
In the field area of the query
Hutchins:=IIf[table]![division]="43",[table]![Order Quantity]/1000*[table]![Price/M]