MS Office User

Calculated Data Types in Microsoft Access 2010

by on Aug.01, 2012, under Access, Office

Storing calculated values in tables is not usually good practice, 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 the table by using the Data Type Calculated.

For example: (See Video above for Step by Step)
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 & Time, etc.) a single Date and Time field will be required to complete the calculations.

In prior versions of Access this would have required creating expressions in queries to add the two fields together.
Microsoft Access 2010 offers the option to create the expression at the table level.

  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
  7. Save the table

The Calculated Field value will now be available to use in queries or for display on forms and reports.

Word of Caution:

Use the Calculated Data Type selectively, sparingly and ONLY when the Components in the expression will remain STATIC.  That is the Components will NEVER change.

  • An Individual’s Date and Time of Birth will never change.
  • The Discount Percentage on a order may possibly change over time and therefore ALL values in a Calculated field will reflect a change 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 remote car starter.  Great option to have but not a necessity or the only way to start the engine.

Random Posts:

  1. Creating a Macro to Save a Report as PDF in Access 2010
  2. Creating Screen Clippings (screenshots) using OneNote 2010
  3. Using Text Box controls to create borders in Microsoft Publisher 2010
  4. Connecting Microsoft Outlook 2007 to GoDaddy email with SSL
  5. Top 10 Reasons to get Microsoft Office 2010!
  6. The Access 2010 Programmer’s Reference gets a 5-star review!
  7. How to Enable the Developer Ribbon in Microsoft Excel 2010
  8. The new msOfficeUser.com site is up and running!
  9. Showing Hidden and System Objects in the Navigation Pane
  10. Creating a Spreadsheet from Template in Microsoft Excel 2007

:, , , , , , ,

Comments are closed.



Archives