Query Condition

[aerosmith]

I currently have the following expresison in a query

Rcode: Nz(DLookUp("Trans_Code","[Fee Reversal Transaction Codes]","Trans_Code='" & Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+1,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) & "'"),999)Rcode: Nz(DLookUp("Trans_Code","[Fee Reversal Transaction Codes]","Trans_Code='" & Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+1,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) & "'"),999)


What it does is compares a field to a table and if nul it should spit out999
it truncares the field as its a multivalue field,
Everything is working ok except i want it to also
compare to see if the code is blank if so i want to assign it a value of 999


I have done this before via another table, but i cant get seem to get the syntax correct to add itto the expression

the following code does the trick

IIf(Len([Customer_Action_Req_d_Reason] & "")=0,"999",[Customer_Action_Req_d_Reason])


Anyone know how to merge both of these to make it work?


Thanks for your time.

 

[aerosmith]

Or if not that statment, then somehow add to the express if blank then assign 999