David J Murray Dev
~ Learn and Do - Excel and Share ~

MS-Access 2010 and Calculated Fields

Oct 31, 2010
You're Reading: MS-Access 2010 and Calculated Fields

The new version of MS-Access (2010) has the ability for calculated fields.

You may already know this concept as virtual fields, I-Descriptors, derived fields, or if you are rather long-in-the-tooth; correlatives within U2. So, does MS-Access 2010 have the same capabilities as virtual fields within U2?

So, what can be done with calculated fields within MS-Access 2010?

Compared to the virtual fields within the U2 range of products, it is actually very similar.When the field is created, a formula is specified via the MS-Access Expression Builder dialog window.

To see how far I could take this new feature, I created a table and added a series of simple fields:

MS-Access 2010 Table Definition Summary
Field Name Field Type Field Formula
ID AutoNumber
F1 Text
F2 Text
CalcAdd Calculated Int([F1]) + Int([F2])
Integer1 Calculated [F1]
CalcAddNest Calculated Int([Integer1]) + Int([F2])

To keep it as simple and as similar to U2 as possible, I created two data fields – F1 and F2 (strange that) – as text fields. I then added a simple addition field – CalcAdd – which adds F1 and F2 together. As the data is stored as text, I needed to convert to integers before adding. I am assuming that the numbers are stored normalized as integers as they would be within a U2 database. This worked correctly and displayed the total of F1 and F2.

Next, I wanted to know if it is possible to use a calculated field within another such field; similar to how a synonym is used within U2. That is the field Integer1 in the table above. This also worked within queries, reports and forms as expected; it would display the contents of field F1. Therefore, from a U2 perspective, it is possible to create a series of fields within a table as F1,F2..Fn and use calculated fields as synonyms within the table for data display and calculation manipulation; similar to correlatives and conversions.

The final field is the CalcAddNest, in the table above. This field uses a calculated field and a data field in a calculation; a nested or recursive arrangement. Again, the result displayed correctly.

For a SQL query, the names of the fields are used normally; there is no special requirements for a calculated field. And this is the same for a report.

The use of calculated fields within forms is however somewhat different, but expected. The calculated fields are display only and are interactively updated as the underlining data fields change. This is as expected for virtual or correlative fields within U2, but as the synonym field is implemented as a calculating field it cannot be used as a input data field as would be possible within SB+ and other similar form generators within U2.

So, where does this leave us? Well, as multi-values and now calculated fields are available in MS-Access 2010, and if your application uses a parameter driven 4GL or a series of menu, form, and report parameter driven generators, it should be possible to convert the application to run within MS-Access 2010. Well, at least most of the heavy lifting could be automated.

For instance, SB+ has parameter driven screens, queries and reports, based on a meta-dictionary arrangement for the fields and files. The SB+ basic, which is a reduced set of the underlining UniBasic, could be converted to macros or VBA within MS-Access 2010, and a similar procedure for the SB+ processes. Finally, the SB+ meta-dictionary of the files could be used to build the tables, data fields and calculating fields with the MS-Access 2010 application.

This could be rather useful for a demo system of the U2 based application, or as the run-time of MS-Access 2010 is free, a cheap, small, entry-level version of the enterprise U2 based application.


A link to the Microsoft website with an example and comments – http://msdn.microsoft.com/en-us/library/ff945943.aspx#Y1368.

About the Author

<a href="https://davidjmurray.dev/losucag/davidjmurray/" target="_self">djm</a>


I have been in the computer/IT industry for over 35 years. After spending half a life-time in highly proprietary database environments, I have succumbed to the WordPress Eco-system and I'm well down the chasm. I am specializing in AWS hosting, Wordpress CMS, Elegant Themes, in particular Divi and Extra themes.


Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Pin It on Pinterest

Share This