Tip of the Month (November 2000)

Using Lookup Values in Calculations

Calculated controls can only get their values from the current record (or variables). If you want to use a value found by a lookup control in a calculation, connect the lookup control to a column in the form's table (or a variable), and then use that column (or variable) in the calculation.

For example, assume CostPerDose comes from the DrugPrice table. Also assume you are building a form over a Medication table. The Medication table has the columns DrugName, DosesPerDay, and NumberOfDays. Add another column to Medications called CostPerDose. Create a lookup control (to retrieve DrugPrice.CostPerDose), and choose YES when asked if you want the lookup value saved in the application table (save it in the CostPerDose column of Medication). Then you can use the calculation: CostPerDose * DosesPerDay * NumberOfDays