Problem with IIF Expression in access 2010 report (1 Viewer)

[lwallis]

My report is run from the main form: The user selects a branch and clicks a button that runs the report for the branch that was selected.

The report contains employee cost information and the employee's role.

In the header section of the report I created a text box with the following Expression: =IIf([Role]="Division Manager",[EmployeeName],"")
--- If the field in the detail named Role contains "Division Manager" then display the employee name in the text box, else display nothing.

For some reason it only works for some of the branches!

There are 6 branches that have employees with the role "Division Manager" but only 2 show up in the text box.
For example:
In the main form, when I select branch 120 and click to run the report, I get the results I want in the detail (including an employee with the role "Division Manager" but the division manager doesn't populate in the header.
Yet, in the main form, when I select branch 910 and click to run the report, I get the results I want in the detail and the division manager in the header!

When I look at the query results that the report is based on, all the information I need is there.. I can't figure out why some fill the text box and some don't Ugh!

I hope it's something silly I've missed.

Thank you for your help
Frustrated Lynda

 

[MarlaC]

The first thing I'd check with an issue like this is that "Division Manager" is actually that in all cases, not "Division Manager " or " Division Manager " or "Division Mgr." You could try something as simple as changing your Iif expression to a wildcarded version that would cover those possibilities and see if that makes the difference.

=IIf([Role] LIKE "*" & "'Division Manager'" & "*",[EmployeeName],"")

 

[lwallis]

It is the same. It is a combo box selection the user chooses and the ID is stored in the employee table.

Here is another piece to the puzzle: The records that work have the Division Manager listed first so it appears that the iif statement is only looking at the first record for each branch.

Is there a way to loop the iif statement? or is there a better way to do this?