Solved

Access -- Can't create calculated field

Posted on 2014-04-29
5
3,369 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows Standards for file storage. 3 27
Nest Related files not working in Visual Studio 4 77
Poor Man's .NET Framework Certification 2 59
PL SQL Developer 7 37
This article describes how to programmatically preset the "Pages per Sheet" option that's available with most printer drivers.   This setting lets you do "n-Up" printing, where two, four, or more pages are printed on each sheet of paper. If your …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

825 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