Dlookup fails in Calculated field

[Keith]

I have the following code in a calculated field in a query which is the record source for a form.

Test: IIf(IsNull([NatSubsBranch]),"",IIf([NatSubsBranch]=1,"Standing Order",IIf([NatSubsBranch]=_
[BranchID],"",DLookUp("Branch_Name","tblBranch","BranchID=NatSubsBranch"))))

When I run the query I get an error 'Access cannot find the field NatSubsBranch'. I have tested each part of the code and it's failing on the Dlookup part.

However if I insert the following code as the record source of an unbound text box on the form I get the correct result. Can anybody tell me why please?

=IIf(IsNull([NatSubsBranch]),"",IIf([NatSubsBranch]=1,"Standing Order",IIf([NatSubsBranch]=_
[BranchID],"",DLookUp("Branch_Name","tblBranch","BranchID=NatSubsBranch"))))

 

[Ari]

Hi

Try this:

Test: IIf(IsNull([NatSubsBranch]),"",IIf([NatSubsBranch]=1,"Standing Order",IIf([NatSubsBranch]=_
[BranchID],"",DLookUp("Branch_Name","tblBranch","BranchID=" & [NatSubsBranch]))))

 

[Keith]

Thanks Ari, that did the trick.