Solved

Access -- Can't create calculated field

Posted on 2014-04-29
5
4,191 Views
Last Modified: 2014-05-01
What am I doing wrong?

Screen shot with the error message
0
Comment
Question by:rrhandle8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40
ID: 40031359
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
 

Author Comment

by:rrhandle8
ID: 40032140
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
 
LVL 40
ID: 40032344
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 300 total points
ID: 40036072
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
 

Author Closing Comment

by:rrhandle8
ID: 40036088
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question