FreeOfficeTutorials.com logo

Microsoft Access 4D: Calculated Fields in Queries / Reports

Overview

Calculated Fields in Queries:

  • In tblTransactions we have a field named borrowdate which records the date that a movie was rented out.
  • We need to be able to calculate how many days a movie has been out for in order to charge an amount for each day.
  • Look at the following fields below and locate them on the figure:
    • Today (this changes everytime you open the following)
    • DaysOnLoan (this takes Today and subtracts BorrowDate to determine the amount of days that a movie has been out)
    • MovieFees calculates the Price per day times the DaysOnLoan

CalcQuery

Problem

  • You might think that these three fields (Today, DaysOnLoan and MovieFees) could be added to tblTransactions but this is not possible!
  • Why?
  • Because these fields change depending on which day you open the database and information in tables is not updated automatically....(remember that one way to change data in tables is to use an update query, however in this case a calculated query is more useful)

Solution

  • A query can be made with all the information from tblTransactions and calculated fields can be created in the query itself (the information below is a query not a table)

CalcQuery

Creating a Calculated field:

Example

Today: Date()

The new field above is named Today and a function... Date() ...is used to calculate today's date

Example

DaysOnLoan: [Today]-[BorrowDate]

The new field above is named DaysOnLoan

Note to create a calculated field you give it a name like any other field and you put a colon : after the name and then put the calculation in

In the DaysOnLoan query above it takes the field Today which you just created and subtracts the BorrowDate that was already in tblTransactions

In our movie store we will charge a customer for one day rental when they leave the store, however if they have late fees the calculated query to determine the amount of days late (not paid for) is:

DaysOnLoan: [Today]-[BorrowDate] -1

Note the -1 portion subtracts the day that has already been paid for.

If you want to determing the MovieFees for a current rental you need to change the above formula to the following:

DaysOnLoan: [Today]-[BorrowDate] +1

Note the +1 portion adds the one day that the customer will pay, otherwise the above formula results in 0 days

Brackets

  • If you are using other fields in a calculated fields you must place them in square brackets as shown in red above

Colon

  • A colon : always follows the name of a newly created calculated field as shown in red above

Function

  • =date() is a function, similiar to =page() and other functions that can be placed on reports as you have seen
  • a function starts with an equal sign or can be used in a calculated field such as Today: Date()

stepbystep


This website is copyright protected see contact page for details.

 

stepbystep