changing label's caption according to field's values in a report

Question

Hi

I have field1, field2 , .... ,etc. in a table, and I have a report based on that table.

Field1 takes only one of three values ( A, B ,or C e.g.)

The report contains a text box control ( txtField1) bounded to field1, and an unassociated label control.

How can I change the label's caption according to txtField1's value in print preview ?

For example; How can I let the report (when I open it in print preview) show the text "Number 1" in the label control for group A,"Number2" for group B, and "Number3" for group C

In other words, I am looking for a way  that effecting like conditional formatting, but to change label's caption rather than its formatting.

Thanks.

Answer 1

I'd use a text box instead of a label. You can then set the Control Source of this text box to

=Switch([field1]="A","Number1",[field1]="B","Number2",[field1]="C","Number3")

Even better, create a small lookup table, with the distinct values of field1 in one field (the primary key), and the corresponding text strings in the other field.

Create a query based on the original table and the lookup table, joined on field1 and the first field of the lookup table.

If field1 can be empty, double-click the join line and select the option to return all records from the original table.

Add all fields from the original table plus the second field from the lookup table to the query grid.

Use the query as record source for the report.

You can now use a text box without label bound to the new field.