ACCESSでの桁数を指定して数値を抜き出し、カンマ区切り表示にする方法

質問

ACCESSでのデータ変換について質問です。
下記のような半角20桁の数値が入力されているフィールドが1～10まであり、
これらの値の左側から7桁目までの数値を抜き出し、それら抜き出した
数値の1桁ごとに「,」で区切って表示したいと思っているのですが、
一括括変換する方法があれば教えて下さい。

例(1）12345678910000000000　→1,2,3,4,5,6,7
例(2）00000000001234567890　→0,0,0,0,0,0,0
例(3）00112233445566778899　→0,0,1,1,2,2,3

私は、アクセスに不慣れなので、数式を入力する方法も合わせて教えて
いただけると大変助かります。
MID関数を使えばいいらしいのですが、ACCESSではどうすればよいのか分からないため質問させていただきました。
よろしくお願い致します。

回答No.5

30246kiku · ベストアンサー

＃４です

> 前回教えて頂いたSQL文の後ろに追加でよろしいでしょうか?

紛らわしい書き方しましたね、すみません。

＃１で示したものへの、部分的修正個所を書いてました。

標準モジュールに記述するのは、以下となります。

Public Function CutAndKamma(vQ As Variant) As String
　　Dim sRet As String
　　Dim i As Integer, j As Integer
　　Const MaxMojiLen As Integer = 7　'★桁数指定

　　On Error Resume Next
　　sRet = ""
　　If (Not IsNull(vQ)) Then
　　　　j = Len(vQ)
　　　　If (j > MaxMojiLen) Then j = MaxMojiLen
　　　　For i = 1 To j
　　　　　　sRet = sRet & "," & Mid(vQ, i, 1)
　　　　Next
　　End If
　　If (Len(sRet) > 0) Then sRet = Mid(sRet, 2)
　　CutAndKamma = sRet
End Function

※クエリへの記述は　　CutAndKamma(テーブル名.そのフィールド名) AS XXXX
のように変更ありません（クエリをSQLビューで見た時の）

※桁数を変更する時には、★部分の 7 を書き換えるだけです。

お礼

30246kiku様
いつも丁寧なご回答をありがとうございます。
早速、試したところ希望通りの結果を得ることができました。
こんなに複雑なSQLを作っていただき、大変感激したと同時に
とても勉強にもなりました。ぜひ活用させて頂きますね(*^_^*)
この度は、Access初心者のため理解不足などで多々
ご迷惑をお掛けしてしまい申し訳ございませんでした。
また質問等をさせていただくこともあるかと思いますが、
宜しくお願い致します。

回答No.4

30246kiku

＃２です

> 構文エラー：クエリ式に演算子がありません（構文エラー）

このメッセージで一緒に表示されていた文字列は何でしたか。

クエリ式 '・・・・・・・・・・'の　構文エラー：演算子がありません。
の　・・・・・・・・・　部分になります。

良く間違うのは、(　)　や、スペースを全角にしてしまうとか、、、（半角で記述します）
で、そのメッセージを閉じた時、どの部分が反転表示されていますか。
その近辺でエラーとなっています。（反転表示されない時もあります）

※　実現するための方法、やり方は１つということはありません。
複数フィールドに対して同じ処理をしたいということだったので、今後の修正/変更のしやすさからユーザ定義関数を使う方法を提示させていただきました。
現在は７文字ですが、５文字、１０文字に変更する時は１箇所変更することで対処できます。
この変更くらいでは、不具合を作りこむことはまずないと思います。
（今後変更なければ、余計なお世話ということに）

処理を確かにするためには、フィールド内の文字の桁が７に満たない時の処理を盛り込むくらいでしょうか。
（それはない、であれば今のままでも）

　　Dim i As Integer, j As Integer
　　Const MaxMojiLen As Integer = 7

　　On Error Resume Next
　　sRet = ""
　　If (Not IsNull(vQ)) Then
　　　　j = Len(vQ)
　　　　If (j > MaxMojiLen) Then j = MaxMojiLen
　　　　For i = 1 To j

とか、

※
実物を見ることができないので、メッセージに表示される内容、メッセージを閉じた後で反転表示される近辺など、少しあたってみてください。

どうしてもダメであれば、そのSQLビューの内容をそのまま提示してください。
（わけあって提示できない内容であれば、一度メモ帳にコピーし、フィールド名/テーブル名を置換機能をつかって置き換えたものを提示してください）
（自分で文字消したり追加したりしないでください）
（提示される場合は、テーブルのサンプルも５～１０行程度欲しいです）

補足

30246kiku 様
早々のご回答をありがとうございます。
大変分かりやすいご説明をいただき恐縮でございます。

ご指導いただきました「構文エラー」については、テーブル名とフィールド名の記述に誤りがあったようで、それを修正することで解決いたしました。

今回教えていただきました「文字の桁が７に満たない時の処理」の
SQL文は、前回教えて頂いたSQL文の後ろに追加でよろしいでしょうか?
追加するとコンパイルエラーが発生し、以下のエラーメッセージが
表示されます。
「同じ適用範囲内で宣言が重複しています」

また、今回のSQLだけを記述した場合は、結果が全て「＃エラー」になります。
原因は、私の記述方法にあると思うのですが、アドバイスをいただけると幸いでございます。

お手数お掛け致しますが、宜しくお願い致します。

回答No.3

mumu_bb

横から失礼します。

クエリでしたら、
クエリの新規作成をして、使用するテーブルを選択して、
「フィールド:」のところに、

Format(Left([フィールド1],7),"@\,@\,@\,@\,@\,@\,@")

(フィールド名が不明なので、仮に「フィールド1」としています)
と入力すれば、表示出来そうです。
入力後、
式1:
みたいなものが先頭に付きますが、そのままにしておいてください。

フィールド10個あるようなので、
右隣の枠に他のフィールドも同じ様な形式で入力していってください。

あと、一括変換したいとのことですが、
テーブル作成クエリで、新しくテーブルを作られてはいかがでしょうか？

Access2000～20003では、
テーブル作成クエリは、
メニューバー　から　クエリ(Q) →　テーブル作成(K)
で、未使用のテーブル名を入力し、
再度、メニューバー　から　クエリ(Q) →　実行(R)
で、新しいテーブルが作成できます。

お礼

ご回答くださり誠にありがとうございました。
希望通りの結果を得ることが出来ました。
設定画面の添付画像は、初心者の私には大変分かりやすくて参考になりました。
ありがとうございましたm(__)m

回答No.2

30246kiku

＃１です

> 数式を入力する方法も合わせて教えて
失礼しました。

ユーザ定義関数は記述できたものとして、以下手順を。

クエリのデザイン画面を開いているとします。

そこには、対象のテーブル/クエリが四角いテーブルの様な形で表示されているとします。

変更したいフィールドを１つダブルクリックすると、下の方にそのフィールドが表示されると思います。

ここで、クエリをSQLビュー表示に切り替えてみます。

そこに表示されているのは、

SELECT テーブル名.そのフィールド名
FROM テーブル名;

のようになっていると思います。

ここを自力で、

SELECT CutAndKamma(テーブル名.そのフィールド名) AS XXXX
FROM テーブル名;

と書き換えてみます。

その後、クエリをデータシートビュー表示に切り替えてみます。

すると、７つの文字がカンマ区切りで表示されると思います。
その時に表示されるフィールド名が、XXXX になります。

クエリをデザインビュー表示に切り替えてみます。

すると、フィールドの欄は

XXXX: CutAndKamma(テーブル名.そのフィールド名)

に書き変わっていると思います。

で、これをメモ帳などに張り付けて、XXXX の部分、そのフィールド名の部分を変更したものを、隣のフィールド欄に張り付けていきます。

＃１　で示したものは、SQLビュー表示での書き方になります。
（デザインビューを見たまま説明するのが難しいので、クエリの内容についてはSQLビュー表示での書き方を示すことが多いです）

※その変更したいフィールドが１０個あるようなので、XXXX も１０通りの名前に変更します。
（同じ名前：フィールド名は付けれないので）

補足

30246kiku 様

早々の御回答をありがとうございます。
ご指示いただきました手順で「SQLビュー表示」画面で、
テーブル名やフィールド名を書き換えた上で、クエリを
データシートビュー表示に切り替えようとしたのですが、
「構文エラー：クエリ式に演算子がありません（構文エラー）」
と表示され、次に進むことが出来ませんでした。

原因として考えられることがあれば、教えて頂けると幸いです。

度々お手数お掛けして申し訳ございませんが　なにとぞ宜しくお願い致します。

回答No.1

30246kiku

その数値が入っているフィールドの型はテキスト型ですよね。

編集用にユーザ定義関数を、標準モジュールに作成します。（以下例）

Public Function CutAndKamma(vQ As Variant) As String
　　Dim sRet As String
　　Dim i As Integer

　　On Error Resume Next
　　sRet = ""
　　If (Not IsNull(vQ)) Then
　　　　For i = 1 To 7
　　　　　　sRet = sRet & "," & Mid(vQ, i, 1)
　　　　Next
　　End If
　　If (Len(sRet) > 0) Then sRet = Mid(sRet, 2)
　　CutAndKamma = sRet
End Function

クエリでは、編集の必要なフィールドに対して上記関数名で呼び出します。

そのフィールド名を F1 と仮定すると、

CutAndKamma(F1) AS XXXX

XXXX は新しいフィールド名

関数の内容は、１文字づつカンマを先頭に付けた文字列を生成し、
文字列が生成されていたら先頭のカンマを取り除いたものを戻り値として返す。
というものです。

※　関数名は適宜変更してください。

補足

初歩的な質問で恐縮なのですが、【CutAndKamma(F1) AS XXXX】は
どこに記述すれば宜しいでしょうか?
また、「XXXX は新しいフィールド名」とは、新たにフィールドを追加し、そこに【CutAndKamma(F1) AS XXXX】を記述するのでしょうか?

お忙しい所 大変恐縮ではございますが、宜しくお願い致します。