How do I query multiple fields into one-CodePudding

I am trying to create a query that returns each comma-separated value into one column

TEXT
A1,A2,A3
X,D3,PP
FF
DF(a),R

This is what I have tried. Below script extracts each comma-separated value (which is never more than 3) into 3 columns and tries to UNION THEM

SELECT IIf(InStr([A],",")='0',[A],Left([A],InStr([A],",")-1))  FROM Tbl
UNION ALL
SELECT  IIf(InStr([A],",")='0','',Mid([A],InStr([A],",") '1',Len([A])-InStr(Len(IIf(InStr([A],",")='0',[A],Left([A],InStr([A],",")-1))) '2',[A],","))) FROM Tbl
UNION ALL
SELECT  IIf(InStr(Len(IIf(InStr([A],",")='0',[A],Left([A],InStr([A],",")-1))) '2',[A],",")='0','',Mid([A],InStr(Len(IIf(InStr([A],",")='0',[A],Left([A],InStr([A],",")-1))) '2',[A],",") 1)) FROM Tbl;

When running the query I get an error Data type mismatch in criteria expression The formulas work correct in the following query

SELECT [Tbl].A, 
IIf(InStr([A],",")='0',[A],Left([A],InStr([A],",")-1)) AS a, 
IIf(InStr([A],",")='0','',Mid([A],InStr([A],",") '1',Len([A])-InStr(Len([a]) '2',[A],","))) AS b, 
IIf(InStr(Len([a]) '2',[A],",")='0','',Mid([A],InStr(Len([a]) '2',[A],",") 1)) AS c
FROM Tbl;

The desired outcome should be:

TEXT
A1
A2
A3
X
D3
PP
FF
DF(a)
R

CodePudding user response：

Numbers are not text, so remove the quotes:

SELECT IIf(InStr([A],",")=0,[A],Left([A],InStr([A],",")-1)) 
FROM Tbl
UNION ALL
SELECT IIf(InStr([A],",")=0,'',Mid([A],InStr([A],",") 1,Len([A])-InStr(Len(IIf(InStr([A],",")=0,[A],Left([A],InStr([A],",")-1))) 2,[A],","))) 
FROM Tbl
UNION ALL
SELECT IIf(InStr(Len(IIf(InStr([A],",")=0,[A],Left([A],InStr([A],",")-1))) 2,[A],",")=0,'',Mid([A],InStr(Len(IIf(InStr([A],",")=0,[A],Left([A],InStr([A],",")-1))) 2,[A],",") 1)) 
FROM Tbl;