Query to return a field to determine value for report

[fearoffours]

I have a field on a report which I am struggling to produce the right query for.

I have a table - tblTillDept which consists of a 4 fields. The first is the PK, which is the number of a till department. The second - fourth fields are names of 3 different branches of the small garden centre I am writing the database for. The values in these fields are the names of the corresponding departments. Not every branch has exactly the same name for each field.

Prior to the report being is run, a small form, frmCentre pops up asking the user to select the Centre they are printing the labels for. They select the name of the branch from a combo box (cboCentre) bound to a field list of tblTillDept. When the report is formatted, the intention is that the value of this combo box selects the field from which the value for the department is taken. I can see that I need to set the control source of this property to some sort of query, but I can't quite work out how to do it.

I've uploaded the database, written in Access 2000, here as it's too big to post as an attachment.

 

[fearoffours]

I solved this particular problem by setting a public variable, rather than using a bound combo box. The report then simply calls the variable.