Forming expressions.

Calculated fields are fields obtained from the result of an expression.

An expression is formed by combining an operator with one or possibly two terms. The majority of the time a term will be a field from the table, and the other a concrete value or another field.


operator +, is used to add numbers.

operator -, is used find the difference between two numbers.

operator *, is used to multiply two numbers.

operator ^, is used to elevate a number to the potency of the exponent ( number ^ exponent )

operator /, is used to divide two numbers and to obtain the result in floating point.

operator \ , is used to divide two numbers and obtain an integer result.

operator Mod, divides two numbers and returns only the remainder.

The concatenation operator & is used to concatenate two strings. It is the linking together of strings.

A term can be the name of a column, an expression, a concrete value, a function.

Concrete values

Concrete values need to be written according to the following rules:

Numeric values are indicated by typing the number and no more.The symbol defined in our Windows Regional Configuration needs to be used to separate the whole part of the decimals; the number values may also not be written formatted, we cannot write them with a thousand'th separator. E.g a correct expression may not be written (sales + 1,000,000), it needs to be written as (sales +1000000).

Text type values are always enclosed in single quotation marks.

Example: 'DENVER'

Date values are written enclosed in # , for example #05/15/06#

Predefined functions.

Access2003 has many predefined functions that you can use, counting and explaining them all will be too long. The best is to know that we have many functions at our disposal and when we want something different we should consult the Access help to see whether a function already exists for what we want to do.

For example we have some that are used more often than others.

DATE() returns to the day we are on

NOW() returns to the day and actual time

YEAR(datevalue) returns the year of the datevalue

MONTH(date) returns the month of the datevalue

CDATE(literalvalue) converts the literal into a date value.


Field names.

Fields' names are indicated typing the name of the field we want to reference to and if this name includes blanks we must enclose it in brackets [ ].

E.g to refer to the City field we can write City or [City], but in the Date of birth field we always write [Date of birth]


Use of the parentheses.

When we combine various expressions we can use parentheses to establish precedence of operators.


Example: sales + (sales * 0.1)


The use of the parethesis serves so that the expression is a little clearer especially when we are combining many expressions, and so that the operators perform in the order that we want in case we forget the priority of the operators.

If we do not use parentheses operations will be calculated in this order:


Multiplication and Division

Addition and Subtraction

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.