When working with extracting fields for Crystal Reports or more importantly for Mail Merge. It may be useful to do some pre-formatting or calculation of the result fields, rather than trying to do this in Crystal or Word.
It is possible to perform some calculations on result fields by the following methods:
Calculating and grouping
You can select operations from the Add button to group certain values together or create simple arithmetic formulas, such as multiplying two fields together.
In this example, we are added a count of the number of times the student has attended class, as well as . Which results in something like:
Used in combination with certain selection criteria, you can limit this to the number of attendances by all students in their lifetime, for a certain venue, a certain month in each year and so on.
Formatting Dates and Complex Expressions
You are also able to directly manipulate the field values to make use of Access SQL functions in your results:
By clicking on the field in the right-hand column, the field value {Attendance Date} is replaced with @1, this denotes where the field will be inserted in your formula. In the example above, we have told Martial Class™ to supply the date in the format:
<4 digit year> - Fred - <day of month, no leading zero>
The results of this formula come back as:
See Named Date/Time Formats and Custom Date/Time Formats for more information about such formatting, but bear in mind this is not limited only to formatting, almost any custom formula can be created here according to general JET 4.0 and ADO SQL syntax and supported functions.
