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.
Best Answer
If I had to guess I'd say that the datatype of transactionnotes does not match the datatype of code. I suggest trying ...
or perhaps it should go the other way using CStr() instead of CInt() for both transactionnotes and code. Try it out and see.