UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Calculated Data Types Microsoft Access 2010    
Calculated Data Types Microsoft Access 2010

Storing calculated values in tables has usually been discouraged by developers, however once in a great while there may be a valid reason to do so.
Microsoft Access 2010 has the ability to calculate and store a value in a table by using the Data Type Calculated.

One example:

There is a client requirement to have separate Date and Time controls in a data entry form.
If there will be a need in the database application to calculate Time Intervals
(i.e. Age in yrs,mos, days, Elapsed Time, Projected Dates AND Time, etc.)
a single Date/Time field will be required to complete the calculations.

In prior versions of Access this would have required creating expressions in a query to add the two fields together
and an additional query to use that calculated value in a Function like DateDiff().

  • 1. In Design View of the table enter the Field Names
  • 2. Save the Table
  • 3. Enter the Name of the Field which will be Calculated
  • 4. Select Calculated as the Data Type
  • 5. The Expression Builder will pop up
  • 6. Build the expression using fields from the table
  • 7. Save the table

The value in the Calculated Data Type Field value will now be available to use in queries or to display on forms and reports.
Calculated Data Types How-To Video

Word of Caution:

    • Use the Calculated Data Type in an Access database selectively, sparingly and when the Components in the expression will remain STATIC.
      That is-- the Components will NEVER change or the Integrity of the Data will not be compromised by a change.
    • The Date and Time of an Appointment may change(be edited), but the Calculated Value, created by adding together the values in these two fields,


will not compromise the integrity any data results obtained by using the Calculated Value in a !uery or Report.

The Discount Percentage on a order may and quite possibly will change over time
and therefore ALL values in a Calculated field will reflect changes made to any of the components of the expression.
Any previous data would then be erroneous.

The Calculated Data Type is not meant to replace queries.
Think of it as a handy time saving gadget—like a garage door opener—great option but not the only way to get into the garage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,331 times.  This page was last modified 13:20, 5 September 2012 by Cpetermann.   Disclaimers