Solved

Access -- Can't create calculated field

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

Screen shot with the error message
0
Comment
Question by:rrhandle8
  • 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now