Use calculated fields in Access queries and reports.
SQL Expression field versus a Crystal Reports formula field Often an SQL Expression field and a Crystal Reports formula field can accomplish the same goal. For example, a report design requirement is to display the first 3 letters of the first name of an employee. A formula field or an SQL Expression field can fulfill this requirement.
Calculating group subtotals and report totals.. , Access automatically restricts the records to those in the current group. For example, the Sum() function totals the values of a field for all the records in the group. To subtotal the amount paid for each product in the current group, you use the following expression in a text-box control.
The Fields collection is the set of fields specified by the dataset query plus any additional calculated fields that you create. After you create a dataset, the field collection appears in the Report Data pane. A simple field reference in an expression displays on the design surface as a simple expression.
Working with Expressions and the Expression Builder. The makes it easy to create expressions. You can add calculations to queries, forms, and reports by typing an expression, or a formula that tells Access exactly what to calculate. An expression can be any combination of values, identifiers (such as the value in a field), and operators that.
SQL statements allow the use of FORMULAS for calculations and text manipulations using operators and functions such as arithmetic operations on numerical field values, concatenation and other manipulations of text field values, date and time arithmetic, and so on. The syntax is rather different from that used in spreadsheets (Calc), and each database 'engine' has slightly different syntax.
If you create a calculated field in a query, you can use any function built-into Access, available in the VBA Library, or your own, custom VBA-Function. However, when creating a calculated column in a table, the available functions are very limited.
However, to cause the contents of those fields to be displayed, you add controls to the report (or form), and bind each control to a field. It is good practice to ensure that the names of the controls are different from the names of the fields (unfortunately, Access has a tendency to make them the same, which can be very confusing).