I'm having trouble working out the details with an IIF containing some lookup statements.

The setup:

Dataset 1: Transactions
Fields: itemid, transactionnotes, rdesc

Dataset 2: EmployeeInfo
Fields: code, fname, surname

I have an expression column with the following:

=iif(Fields!itemid.Value = 100785, 
    "Allocated to: " 
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!fname.value, "EmployeeInfo") 
    & " " 
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

itemid = 100785 indicates a line in the DB where it records a transaction allocated to another staff member. In all isntances where the itemid = 100785 the transactionnotes field (datatype string) will contain a numeric value of who it was allocated to. For those lines, this expression is working exactly as intended.

The problem:

For rows that do not contain itemid = 100785, the rdesc value should just be a string description of the transaction. For about half the rows this is working fine. But for about half the rows it shows #error. I've dug into the DB and the transactionnotes.value is NULL for all the rows getting #error. But its also NULL for the rows that are working. I'm including a screenshot so you can see the relevant values for itemid and rdesc for some rows that work and some that get #error. Description Calc is the field with the expression.

enter image description here

1 Answer 1


If I had to guess I'd say that the datatype of transactionnotes does not match the datatype of code. I suggest trying ...

=iif(Fields!itemid.Value = 100785, 
    "Allocated to: " 
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!fname.value, "EmployeeInfo") 
    & " " 
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

or perhaps it should go the other way using CStr() instead of CInt() for both transactionnotes and code. Try it out and see.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.