我正在嘗試創建一個查詢，將每個逗號分隔值回傳到一列
| 文本 |
|---|
| A1,A2,A3 |
| X,D3,PP |
| 法郎 |
| DF(a),R |
這是我嘗試過的。下面的腳本將每個逗號分隔值（不超過 3）提取到 3 列中并嘗試使用UNIONTHEM
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;
運行查詢時出現錯誤Data type mismatch in criteria expression
以下查詢中的公式正確
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;
期望的結果應該是：
| 文本 |
|---|
| A1 |
| A2 |
| A3 |
| X |
| D3 |
| 聚丙烯 |
| 法郎 |
| 后衛（一） |
| R |
uj5u.com熱心網友回復：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/453214.html
上一篇：如何在聯合查詢中使用別名
下一篇：如何展平這個子檔案陣列？