Access 2007 IIF statement not working 100% of the time

Question

I'm having difficulty with an IIf() statement as the control source of a textbox on a report.

The IIf() statement does work on my report, just not 100% of the time. I have taken a screenshot of the problem below.

On my report, if the Supply Locator = the OHQ Locator, I want the Supply Qty to = the OHQ for that locator, else put 0.

The control source of the textbox is =IIf([SUPLYLOC]=[OHQLOC],[QOH],0).

The screenshot shows what lines work and which don't by the green and red rectangles.

Any info or insight appreciated.

Answer 1

I believe the problem was the grouping on the report in trying to work with the IIF statement, but I was able to get it to work with the below. I actually had to reference the field on the report.

=DLookUp("QOH","qryPickList","[SUPLYLOC]=[OHQLOC] AND [COMPNUM] = Reports!rptCustomPickList_main!COMPNUM")