No Expression Builder w/ Calculated fields

[bossmack916]

I am working on a database that someone else created, maybe in Access 2007 not sure. However, I am using Access 2010 and am trying to add a calculated field into my table and the Expression Builder is not populating. If this database were created in Access 2007, would that be the reason it doesnt populate? If not, what could it be?

 

[Beetle]

If this database were created in Access 2007, would that be the reason it doesnt populate?

Possibly, but the more important point is that, although you can create a calculated field in A2010, that doesn't mean you should. Personally, I would advise against it. You should create a query of the table, then place your calculated field in the query and use the query where you would have otherwise used the table (i.e. as the record source of a form, etc.). There are several reasons for this;

• Backward Compatibility. If you create calculated fields in your tables your database will not be compatible with any older version of Access.
  
• Flexibility. Calculated fields in A2010 are limited. You'll have a wider range ways to perform calculations in a query.
  
• Reliability. In A2010, if you change the expression in the calculated field after data has already been entered in the table, the existing data may not be updated correctly, and there is no reliable way to detect, or correct, this problem.

 

[bossmack916]

ive read that before, however, I am trying to generate a report that totals the amount of total production in dollars. On the report now I have a text box named "Total" that has the expression quantity * price in it. Yet when trying to add a group sort in the report, I have no object named "Total" to sum. In addition, since the "Total" field has an expression and not data from a control source, I cant refer to it as an object on the report. I hope that made sense, but im basically trying to generate a report that displays product name, price, quantity, and then order total. Then at the end of the report, a total of the quantity of items purchased and a total in dollar amount of all the orders placed.

 

[Beetle]

That is why I suggested you use a query. Create a query that includes all the fields that you need form the table, then add a calculated field like;

Total: [Qty] * [Price]

Then use that query as the record source for your report. The Total field will be available in the record source just as if it were a field in the table (without the associated draw backs of a calculated table field). You can bind a text box to it in the detail section of your report and you can sum it in the footer of your report.