Need advice on IIF or Dlookup for textbox on report

[sirluke34]

Hello all,
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 (see link 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. I have also tried a Dlookup with the same arguments as above, but it only returns the first correct value in the table for all COMPNUMs (in the example - 640.00).

PickList screenshot

Any info or insight appreciated.

 

[sirluke34]

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")