[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5734
  • Last Modified:

Access -- Can't create calculated field

What am I doing wrong?

Screen shot with the error message
0
rrhandle8
Asked:
rrhandle8
  • 2
  • 2
1 Solution
 
Jacques Bourgeois (James Burger)PresidentCommented:
You cannot enter a calculated expression dependent on other  fields when defining the default value. And there is a very good reason for that. The default value is created at the same time as the records. There are no values in the other fields at that time.

There is no need to store the Profit in the table, it just takes useless space since you can recalculate it in queries, forms and reports that would need it. This is where your expression should be used.
0
 
rrhandle8Author Commented:
So how does one create a calculated field?  I am aware of the extra space it will take up, and seldom use calculated fields.  In this case it will save the user a lot of time which in my opinion is more precious than extra space in the database.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
As I told you, calculated fields are used in queries, forms and reports.

The easiest way to go is to create a query that has all the fields of the table, plus a calculated field. To do that, you simply type your expression instead of the fieldname on the first cell of an empty column in the Query Designer.

Use that query instead of the table in your forms and reports when you need the profit.

A query is like a temporary table and can be used almost anywhere you use a table. And that one will contain a temporary field that exists only in that temporary table.

You also gain an extra feature: calculated fields in a query are read-only, so you are automatically sure that a user won't go in and manually change the value of the profit field. It's usually a good idea however to change the format of these calculated fields in forms so that they do not show the same way as editable fields. Simply setting the background of the TextBox to grey is usually a good indication to the user that he cannot edit that field.
0
 
Nick67Commented:
Something like this is what @JamesBurger has in mind.
MS's rules and methods for creating calculated fields in Access 2010 are here:
http://office.microsoft.com/en-001/access-help/add-a-calculated-field-to-a-table-HA101820564.aspx

I don't think you can do this in Access 2003, and maybe not in Access 2007.
You certainly cannot do it in the manner described in Access 2003 OR Access 2013
It seems to be an Access 2010 thing only and still a bad idea
http://allenbrowne.com/casu-14.html
Profit.mdb
0
 
rrhandle8Author Commented:
That explains it.  The database I was using is a 2003 version, and I was trying to follow the rules for 2010.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now