Compare value , and assign a value if not found in table

[aerosmith]

In a new project i have the following which i cant get to compare properly

SELECT [D 01 RM FR Reason Code Update].*, Left([Customer_Action_Req_d_Reason],InStr(1,[Customer_Action_Req_d_Reason],",")-1) AS Code1, Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+2,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) AS Code2, Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+5,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) AS Code3
FROM [D 01 RM FR Reason Code Update];


What this does is seperates values from a field into 3 new columns.

Code1 Code2 Code3

What i want to do is, 1 compare it to a table to validate the code.
Example
Code1 = 089 I have a table which 089 is a code.
Codes are contained in
Fee Reversal Transaction Codes.Trans_Code

Right now im using the Like criteria, This works for the time being,
But what i really want to do is.

If the code is NOT in the table, Or is not in the like then i want to assign a code as 999


If i need to explain this better, i certainly can try

i appreciate the time someone reads and commends on this

 

[KenHigg]

Something tells me you need to send all of this out to a function...

 

[aerosmith]

I've tried something like this,
SELECT Nz(RT.Code, "999") As DerivedCode1
FROM qselStage1 AS S1
LEFT JOIN RefTable AS RT
ON S1.Code1 = RT.CodeBut i cant get it to work :|

 

[aerosmith]

If comparing to a table is hard, then if its possible to just compare to the following

089 063 45 047 049 066 081 083 085 087 089 091 093 095 614 683 684 686

if its not one of these then the value of the field should then be 999

 

[KenHigg]

I'm guessing you're not up to sending 'Customer_Action_Req_d_Reason' to a function for parsing?

 

[aerosmith]

if i knew how

 

[KenHigg]

Have you ever done a simple function?

 

[aerosmith]

Yes to replace something in a field by forcing.

Public Function RemoveSquares(ByVal strData As String) As String
RemoveSquares = Replace(strData, Chr(10), ",")
End Function

 

[KenHigg]

So how about sending a function 'Customer_Action_Req_d_Reason'. Then do all of that InStr / Mid stuff to it there and have the fuction send back the values(codes) you need in a query. Then you build a second query that links to the table with the code values... Hope all of that makes sense.