Beginning Access 2002 VBA (Programmer to Programmer)

You've already seen expressions in use in this chapter. Remember that an expression is just something that returns a value, and in the following Try It Out we will be using an expression to return the value to the control source property of a textbox that is placed on the report itself. The following are all examples of expressions:

• ccyTotal = ccyPrice * ccyQuantity

• strName = strFirstName & " " & strLastName

• strMonth = Format(datOrderDate, "mmmm")

So far you've seen expressions that use the page number, date, and the Sum function. With the latter, this was placed in the section footer, and produced a total of records in the preceding detail section. You can also do this the other way around, by putting a field in the detail that references the total. For example, let's assume we'd like to see what percentage of a company's total sales occur in each month.

Try It Out-Expressions

1. Switch the form into design view.

2. Place a new textbox on the form, in the Detail section. Place it to the right of the SumOfQuantity field, and remove its label.

3. Put the following in the Control Source property:

   = [SumOfQuantity]/[txtCompanyTotal]

4. Change the Format property to Percent .

5. Add a label to the CompanyName Header section changing its Caption property to % . If you wish add separator lines to break up the headers and totals from the data. Now preview the form:

This just uses the total field we created earlier. So not only can you create new fields with expressions in them, but you can use those new fields in other expressions, too.