Format a multy value field

aerosmith

Registered User.
Local time
Today, 00:17
Joined
Jan 12, 2009
Messages
26
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?
 
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?

I believe that this happens because in the case of "111", the value of InStr(1,[Customer_Action_Req_d_Reason],",")-1) is 0. The IIf command could prove useful here.

Example:

Left([Customer_Action_Req_d_Reason],InStr(1,[Customer_Action_Req_d_Reason],",")-1) AS Code1,

Becomes:

Left([Customer_Action_Req_d_Reason],IIf(InStr(1,[Customer_Action_Req_d_Reason],",")=0, Len([Customer_Action_Req_d_Reason]), InStr(1,[Customer_Action_Req_d_Reason],",")-1)) AS Code1,
 
Last edited:

Users who are viewing this thread

Back
Top Bottom