特定の文字列で列を区切るには？

質問

Ａｃｃｅｓｓ２００３を使用しています。

列Aに以下のような値があります。

列A
1111-222
3333-444-55
6666-777-88-99

これをハイフン毎に列を分けて以下のような形に整形したいのです。

列Ｂ 　 ｜列Ｃ 　 ｜列Ｄ　　｜列Ｅ
1111　 ｜222 　 ｜　　　　 ｜
3333　 ｜444　　｜55　　　｜
6666　 ｜777　　｜88　　　｜　99

クエリなどで一発で実行できるコマンド等があればご教示いただきたく思います。
説明下手ですみませんが、よろしくお願いします。

回答No.5

nicotinism · ベストアンサー

クエリで出来るのかな？出来たとしても難解なものになるのでは？
あの御方なら可能かもしれません・・
直感的に分かりやすいレコードセットを操作する方に決定
説明は最後に
'------------------------------------------------------
Const tblName = "T1"　'操作したいテーブル名の定数

Function getColCount() As Long　'最大の分割数取得
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim t As Long

　　Set db = CurrentDb
　　Set rs = db.OpenRecordset(tblName, dbOpenSnapshot)

　　If rs.EOF = False Then
　　　　t = UBound(Split(rs!F1, "-"))
　　　　Do Until rs.EOF
　　　　　　If getColCount < t Then
　　　　　　　　getColCount = t
　　　　　　End If
　　　　　　rs.MoveNext
　　　　Loop
　　End If

　　getColCount = getColCount + 1
　　rs.Close: Set rs = Nothing
　　db.Close: Set db = Nothing
End Function

Sub makeTable()　 'テーブル操作
Dim db As DAO.Database
Dim rsFrom As DAO.Recordset, rsTo As DAO.Recordset
Dim tdf As DAO.TableDef
Dim strSql As String
Dim i As Integer
Dim tVar As Variant

　　Set db = Application.CurrentDb

'既存テーブルにフィールド追加
　　i = getColCount + 1
　　strSql = "alter table " & tblName & " add column F"
　　For i = 1 To i
　　　　db.Execute strSql & CStr(i + 1) & " text(20) ", dbFailOnError
　　Next i
　　db.TableDefs.Refresh

'各フィールドにF1フィールドの分割した値を格納
　　Set rsFrom = db.OpenRecordset(tblName, dbOpenSnapshot)
　　Set rsTo = db.OpenRecordset(tblName, dbOpenDynaset)
　　Do Until rsFrom.EOF
　　　　tVar = Split(rsFrom!F1, "-")
　　　　rsTo.Edit
　　　　For i = 0 To UBound(tVar)
　　　　　　rsTo.Fields("F" & CStr(i + 2)) = tVar(i)
　　　　Next i
　　　　rsTo.Update
　　　　rsFrom.MoveNext: rsTo.MoveNext
　　Loop

　　rsFrom.Close: Set rsFrom = Nothing
　　rsTo.Close: Set rsTo = Nothing
　　db.Close: Set db = Nothing
End Sub
'------------------------------------------------------
データが入っているテーブルの複製をコピペして、テーブル名を、T1 に
データがあるフィールド名を、F1 に変更

DAOに参照設定がされていなければ・・
Alt + F11 を押してVBEの画面にして
メニュー→ツールから参照設定を選択
参照可能なライブラリから、Microsoft DAO 3.6 Object Library を探して
チェックボックスにチェックを入れます

メニュー→挿入から標準モジュールを選択
新たな窓が出てくるので、上記の点線で囲んだ部分をコピペ
Ctrl ＋ G を押してイミディエイトウィンドウを出して
そこに maketable と入力し Enter

Alt + F11 で Access の 窓に切り替え
先ほどの T1 テーブルを開いてみてください。
なお、DAOのメンバを使うように変数宣言してますので、ADO との共存可能也
また、T1 テーブルにはフィールドは一つしかないものと仮定
文字数ぎり

回答No.11

nicotinism

おお！
またやってしまいましたかorz
試したつもりだったのですが・・・言葉もありません。
鱗の次は冷や汗。。。

piroin654 さん ありがとうございます。
そして、質問者さんごめんなさい。

バグの根っこは、getColCount 関数に有りました。
ついでに見直して データが空（Null）だった場合に対応したつもり。
Nz で囲んだだけです。こんどは大丈夫かな？

Function getColCount() As Long '最大の分割数取得
　　Dim db As DAO.Database
　　Dim rs As DAO.Recordset
　　Dim t As Long

　　Set db = CurrentDb
　　Set rs = db.OpenRecordset(tblName, dbOpenSnapshot)

　　If rs.EOF = False Then
　　　　Do Until rs.EOF
　　　　　　t = UBound(Split(Nz(rs!f1, ""), "-"))
　　　　　　If getColCount < t Then
　　　　　　　　getColCount = t
　　　　　　End If
　　　　　　rs.MoveNext
　　　　Loop
　　End If
　　getColCount = getColCount + 1
　　rs.Close: Set rs = Nothing
　　db.Close: Set db = Nothing
End Function

Sub makeTable()　　 'テーブル操作
　　Dim db As DAO.Database
　　Dim rsFrom As DAO.Recordset, rsTo As DAO.Recordset
　　Dim tdf As DAO.TableDef
　　Dim strSql As String
　　Dim i As Integer
　　Dim tVar As Variant

　　Set db = Application.CurrentDb

　　'既存テーブルにフィールド追加
　　i = getColCount
　　strSql = "alter table " & tblName & " add column F"
　　For i = 1 To i
　　　　db.Execute strSql & CStr(i + 1) & " text(20) ", dbFailOnError
　　Next i
　　db.TableDefs.Refresh

　　'各フィールドにF1フィールドの分割した値を格納
　　Set rsFrom = db.OpenRecordset(tblName, dbOpenSnapshot)
　　Set rsTo = db.OpenRecordset(tblName, dbOpenDynaset)
　　Do Until rsFrom.EOF
　　　　tVar = Split(Nz(rsFrom!f1, ""), "-")
　　　　rsTo.Edit
　　　　For i = 0 To UBound(tVar)
　　　　　　rsTo.Fields("F" & CStr(i + 2)) = tVar(i)
　　　　Next i
　　　　rsTo.Update
　　　　rsFrom.MoveNext: rsTo.MoveNext
　　Loop

　　rsFrom.Close: Set rsFrom = Nothing
　　rsTo.Close: Set rsTo = Nothing
　　db.Close: Set db = Nothing
End Sub

お礼

nicotinismさんをはじめ、たくさんの方より助言をいただき光栄です。質問者です。

nicotinismさんの助言を参考に試行錯誤しておりましたが、望みどおりの結果を得ることができました。大変感謝しております。
レコードが25万件ほどありましたので、一時はExcelでちまちま区切り位置で変換しようとも思いましたが、分かりやすい説明のおかげでAccess上で実行することができました。

ありがとうございました。

回答No.10

piroin654

nicotinismさん　フィールド数がたりません。
＞For i = 1 To i
　For i = 1 To i + 1
なら、正常なんですが。

回答No.9

nicotinism

追伸。nicotinism です。
回答がチンプンカンプンなら・・取り合えず Excel にデータを持って行って
データ→区切り位置で、"-"を指定して分割されたらどうですか？
質問内容からすると、もう御存知のようなのですが一応。

回答No.8

layy

モジュールで自作関数を作る。

Function chkst(st As String, sw As Integer)
Dim x As Long
Dim y As Long
Dim z(10) As String
x = Len(st)
y = 1
z(1) = ""
z(2) = ""
z(3) = ""
z(4) = ""
For I = 1 To x
If Mid(st, I, 1) = "-" Then
y = y + 1
z(y) = ""
Else
z(y) = z(y) & Mid(st, I, 1)
End If
If y > sw Then Exit For
Next I
chkst = z(sw)
End Function

クエリデザインで
Ｂ項目：CHKST("1111-222-333",1)としたら"1111"
Ｃ項目：CHKST("1111-222-333",2)としたら"222"
Ｄ項目：CHKST("1111-222-333",3)としたら"333"

母体文字列を与えて"-"出現何番目区切りを受け取るか、
そんな関数を作れば良いです。

サンプルなので、
CHKST("1111-222",3)
結果が伴わないもの、初期化も省略してます。完全ではありません。

こういうのを考えてアレンジしてみてください。

回答No.7

nicotinism

20246kikuさんへ
なあ～るほど！
眼から鱗が４枚ポロリ。
頭を柔軟にしなくてはいけませんね。
ありがとうございます。

回答No.6

30246kiku

いろいろやり方があると思いますので・・・

標準モジュールに、以下のユーザ定義関数を作成します。

Public Function MojiCut(iNum As Integer, vSrc As Variant, _
　　　　　　　　　　　　Optional sDEL As String = "-") As String
　　On Error Resume Next
　　MojiCut = ""
　　MojiCut = Split(vSrc, sDEL)(iNum)
End Function

"-" で区切った何番目を返すようにします。
何番目は、０スタート

ユーザ定義関数を、クエリから呼び出すようにします。

テーブル名を「TBL_A」と仮定します。

クエリのSQLビュー記述は以下。

SELECT MojiCut(0,列A) AS 列B
, MojiCut(1,列A) AS 列C
, MojiCut(2,列A) AS 列D
, MojiCut(3,列A) AS 列E
FROM TBL_A;

クエリの表示結果は以下

列B　　　列C　　　列D　　　列E
1111　　　222　　　　　　
3333　　　444　　　55　　　
6666　　　777　　　88　　　99

区切りを指定しなければ "-" で区切ったものになります。

MojiCut(0,列A,",") AS 列B

とすると、区切りは "," (カンマ)になります。

上記クエリは、"-" 区切りで４つになることを前提としていますが、
何個に区切られるかわからない時には、VBA でクエリの内容を作成します。

Dim rs As New ADODB.Recordset
Dim sSql As String
Dim i As Integer, iLoop As Integer

iLoop = -1
rs.Source = "SELECT Max(Len(列A)-Len(Replace(列A,'-',''))) AS 数 FROM TBL_A;"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then iLoop = rs(0)
rs.Close

sSql = ""
For i = 0 To iLoop
　　sSql = sSql & ", MojiCut(" & i & ",列A) AS 列" & Chr(Asc("A") + i + 1)
Next
If (Len(sSql) > 0) Then
　　sSql = "SELECT " & Mid(sSql, 3) & " FROM TBL_A;"
　　Debug.Print sSql　' ★ 作成されたSQL
End If

"-" の個数の最大数を求めて、くるくる作成します。
"-" が２６個以上ある時には、Chr(Asc("A") + i + 1)　部分は考えてください。

提示サンプルデータで上記を実行した時に作成される ★部分でのSQLは、

SELECT MojiCut(0,列A) AS 列B, MojiCut(1,列A) AS 列C, MojiCut(2,列A) AS 列D, MojiCut(3,列A) AS 列E FROM TBL_A;

となるので、後はその内容で、
クエリを作成する/既存クエリの内容を書き換える等、
処理を追加すればよいと思います。

参考になる/ならない等々、自己責任でお願いします。

（データ量によっては使えないものかも・・・）

回答No.4

piroin654

No1、No3です。

DAOを使っているので、コード表のツールから
参照設定を選択して、
Microsoft DAO xx Object Library
にチェックを入れて、OKとします。xxは3.6のような数字です。
このとき、
Microsoft ActiveX Data Objects xx Library
にチェックが入っていたらはずしてください。

以上です。

回答No.3

piroin654

訂正です。
コードのSub cmdData()のところを
以下におきかえてください。

Sub cmdData()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim str As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Tデータ", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Tデータ分割", dbOpenDynaset)

If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do Until rs1.EOF
j = 0
For i = 1 To Len(rs1!列A)
If Mid(rs1!列A, i, 1) = "-" Then
j = j + 1
End If
Next i
str = Split(rs1!列A, "-")
rs2.AddNew
For k = 0 To myCount
rs2.Fields(k) = Null
Next k
rs2.Update
rs2.MoveLast
If j = 0 Then
rs2.Edit
rs2.Fields(0) = str(0)
rs2.Update
End If
If j > 0 Then
rs2.Edit
rs2.Fields(0) = str(0)
rs2.Update
For l = 1 To j
rs2.Edit
rs2.Fields(l).Value = str(l)
rs2.Update
Next l
rs2.MovePrevious
End If
rs1.MoveNext
str = ""
Loop
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

No1の続きです。文字数がギリギリだったので。

フォームのボタンクリックで、Tデータ分割という
テーブルにデータを抽出します。
以上です。コードの中で何をしているかの説明は
省きます。何かあれば補足してください。

回答No.2

layy

この際INSTR関数は覚えておく方が良い。

-を違う文字に置き換えたら区切ることできるか。

固定文字列にすれば何文字めから何文字めまでがＢ列、何文字めから何文字めまでがＣ列、となる。

-が何個あるか、あったとして何番目か。１番目見つかるまでがＢ列、２番目見つかるまでがＣ列、となる。

どんな情報があれば、置き換えすれば実現出来そうか考えてみる。

ＶＢＡ、モジュール作成はできますか。

回答No.1

piroin654

クエリでコマンド一発、はありません。多分・・。
あったら、とっくに回答があると思いますが。

(1)
対象となるデータを入れているテーブルを
「Tデータ」とします。フィールドは質問の
ように列Aとします。

(2)
コードで「Tデータ分割」というテーブルを作成します。

(3)
以下のコードを標準モジュールに貼り付けて保存してください。
コード中には、「Tデータ」と「列A」が出てきます。この二つは
実際に合わせて変更しても差し支えありません。

Dim myCount As Long

Sub funcCount()
Dim db As Database
Dim rs As Recordset
Dim i As Long
Dim j As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Tデータ", dbOpenDynaset)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
j = 0
For i = 1 To Len(rs!列A)
If Mid(rs!列A, i, 1) = "-" Then
j = j + 1
End If
Next i
If j > myCount Then
myCount = j
End If
rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

Sub cmdMkTable()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property
Dim i As Long

Set db = CurrentDb
Set tdf = db.CreateTableDef("Tデータ分割")
For i = 1 To myCount + 1
Set fld = tdf.CreateField("列" & i, dbText, 50)
tdf.Fields.Append fld
tdf.Fields.Refresh
Next i
db.TableDefs.Append tdf
db.TableDefs.Refresh
db.Close: Set db = Nothing
End Sub

Sub cmdData()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim str As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Tデータ", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Tデータ分割", dbOpenDynaset)

If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do Until rs1.EOF
j = 0
For i = 1 To Len(rs1!列A)
If Mid(rs1!列A, i, 1) = "-" Then
j = j + 1
End If
Next i
str = Split(rs1!列A, "-")
rs2.AddNew
For k = 0 To myCount
rs2.Fields(k) = Null
Next k
rs2.Update
rs2.MoveLast
If j = 0 Then
rs2.Fields(0) = str(0)
End If
If j > 0 Then
rs2.Edit
rs2.Fields(0) = str(0)
rs2.Update
For l = 1 To j
rs2.Edit
rs2.Fields(l).Value = str(l)
rs2.Update
Next l
End If
rs1.MoveNext
str = ""
Loop
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

(4)
フォームにボタンを一つ設定し、ボタンのクリック時
のイベントに以下を設定してください。

Private Sub コマンド0_Click()
Call funcCount
Call cmdMkTable
Call cmdData
End Sub

以上です