IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org, November 2003. Updated December 2007
How do you get Access to store the result of a calculation?
For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?
The best answer is, "Don't!"
Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.
Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.
So, how do you get the calculated field if you do not store it in a table? Use a query:
This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.
Well, almost foolproof: It is possible to have your calculated fields misinterpreted.
There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.
Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. The calculated field will not work. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.
To achieve this, use the After Update event of the controls on your form to automatically calculate the fee:
Private Sub Quantity_AfterUpdate() Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2) End Sub
Private Sub UnitPrice_AfterUpdate() Call Quantity_AfterUpdate End Sub
Now whenever the Quantity or UnitPrice changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.
If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Calculated Fields'?
26 May 2008
i think that this site is very helpful as i am in the middle of doing prep for an IT SAC that involves access.. there is alot of handy information...
Have your say...