I have the following in a query which seperates a multy value field.
SELECT D-Report.*, 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],",")+1,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-Report];
This works when there is 2 or 3 sets of code example
111,111,111
or 111,111
But does not work for anything not trailing a comma
example 111 shows as null
Is there a way around this?
SELECT D-Report.*, 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],",")+1,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-Report];
This works when there is 2 or 3 sets of code example
111,111,111
or 111,111
But does not work for anything not trailing a comma
example 111 shows as null
Is there a way around this?