アクセスのフォームにカンマ区切りで数値を入力し、そのまま反映させデータ

アクセスのフォームにカンマ区切りで数値を入力し、そのまま反映させデータ出力したいです。

つい先日はお世話になりました。
おかげさまで出したい表を作れるようになりました。

そ、そして今度は、その表をマクロやフォームを使って簡単に出そうと試みています。

JANコードというフォームにJAN「aaaaaaaaaaaaa」を入力すると、
「aaaaaaaaaaaaa」という商品の在庫を所持している店舗を出すというものです。
↓↓↓
--------------------------------
JAN ｜店舗 ｜
-------------------------------｜
aaaaaaaaaaaaa ｜1,2,4,6 ｜
--------------------------------


理想は、カンマ区切りの複数JANをフォームに入力したら、それらの一覧表が出力される仕様です。
↓↓↓
--------------------------------
JAN ｜店舗 ｜
-------------------------------｜
aaaaaaaaaaaaa ｜1,2,4,6 ｜
--------------------------------
bbbbbbbbbbbbb ｜1,3,5,6 ｜
--------------------------------

ですが、今現在は単品でしか表が出せないという問題です。

下記のクエリの抽出条件で、入力した数値を導きだそうとしています。
In ([Forms]![在庫所持店舗表作成]![JAN])

単品なら上手く事が運び、表がでるのですが、
カンマ区切りの複数になると抽出条件が一致しませんとエラー表示が出ます。
どうやら、フォームで入力したカンマ区切りの数値が、
クエリ抽出条件側では「カンマ」が取り除かれています。

フォーム「aaaaaaaaaaaaa，bbbbbbbbbbbbb」
↓↓↓
クエリ上「aaaaaaaaaaaaabbbbbbbbbbbbb」

このクエリで使用しているテーブルには勿論、
26桁のJANなんか存在しませんので、エラー表示です。。

カンマ区切りJAN「aaaaaaaaaaaaa，bbbbbbbbbbbbb」のまま反映させる方法はありませんか？？
複数商品の検索が出来ないとほぼ使い物になりませんorz

アクセス2000を使用しています。
何卒！何卒よろしくお願い致します。。。

No.4 回答者： 30246kiku 回答日時： 2010/10/17 15:57

＃１，２です。

文章書いていたら回答があったみたいですね。
せっかくですので、出来上がったものを回答してみます。


> 抽出時間が大幅に伸びた・・・・この関数を使っているからでしょうか？

そうだと思います。（全件処理していると思うので）

他にもやり方はあるので、試されますか？

１）複数指定する上限（個数）を決めてやる
１－１）JAN 入力の更新後処理でテキストボックスに分割する
１－２）クエリ内で JAN をカンマ区切りで抜き出す
２）クエリの内容を参照＆書き換え実行
３）テンポラリテーブルを使用した絞込みをする

１－２）２）の動くサンプルは私のブログからDLできますが、
規約によりアドレスは書けません。キーワードの提示もできません。


以下に簡単に書いていきます。

１）複数指定する上限（個数）を決めてやる
文字数制限もあるので、
フォーム名を「F1」、検索用テキストボックス名「JAN」とし、上限を５つとします。
上限をもっとしたい場合には同様な記述で追加してください。
（★★ 書いていった結果、文字数オーバしたので２つに分けます ★★）

１－１）JAN 入力の更新後処理でテキストボックスに分割する
フォームにクエリで参照するテキストボックス「tx0」～「tx4」を不可視で配置します。
検索用テキストボックス「JAN」の更新後処理で「tx0」～「tx4」へ分割設定します。

Private Sub JAN_AfterUpdate()
　　Dim sAry() As String
　　Dim i As Long

　　If (IsNull(Me.JAN)) Then
　　　　ReDim sAry(0)
　　　　sAry(0) = ""
　　Else
　　　　sAry = Split(Me.JAN, ",")
　　End If
　　
　　For i = 0 To 4
　　　　If (i <= UBound(sAry)) Then
　　　　　　Me("tx" & i) = sAry(i)
　　　　Else
　　　　　　Me("tx" & i) = Null
　　　　End If
　　Next
End Sub

クエリの変更箇所での記述は、

((_単品在庫リアル.自社コード) In ([Forms]![F1]![tx0],[Forms]![F1]![tx1],[Forms]![F1]![tx2],[Forms]![F1]![tx3],[Forms]![F1]![tx4]))

とします。
IN句のところで、参照するテキストボックスをカンマ区切りで羅列していきます。
（テキストボックス１つに、自社コードが１つ入っている状態にして）

１－２）クエリ内で JAN をカンマ区切りで抜き出す
標準モジュールに以下関数を記述しておきます。

Public Function mySplit(vS As Variant, iNum As Integer) As String
　　On Error Resume Next
　　mySplit = ""
　　mySplit = Split(vS, ",")(i - 1)
End Function

クエリの変更箇所での記述は、

((_単品在庫リアル.自社コード) In (mySplit([Forms]![F1]![JAN],1),mySplit([Forms]![F1]![JAN],2),mySplit([Forms]![F1]![JAN],3),mySplit([Forms]![F1]![JAN],4),mySplit([Forms]![F1]![JAN],5)))

aaaa,bbbb の入力があった場合、
mySplit([Forms]![F1]![JAN],1)　では、aaaa が
mySplit([Forms]![F1]![JAN],2)　では、bbbb が得られます。
第２引数部分が、何個目、　の意味になっています。

必要な分を羅列していきます。書いた分が制限になります。
mySplit([Forms]![F1]![JAN],10)　まで順に書いたとすると上限は１０個に

【つづく】

No.3 回答者： DexMachina 回答日時： 2010/10/17 13:24

確認したところ、クエリの抽出条件でフォームのテキストボックスの

値を参照する場合、Split関数を使用するなどしても、複数要素として
扱うことはできない(=「aaaaaaaaaaaaa，bbbbbbbbbbbbb」は全体で
1つの値としてしか扱えない)ようです。

ただ、VBAから直接SQL文を作成するなら、この縛りから逃れることが
できますので、最初にyukinosuke1104さんが示された通り、「In句」を
使用したSQL文を、VBAから作成するのがよいかと思います。
(In句なら完全一致分しか抽出しないので、上記の問題は発生しない：
　検索条件に入力ミスがあれば結果が表示されないため、入力者が
　ミスに気づきやすい、と)

以下、『在庫所持店舗表データ作成』コマンドボタンのクリックした時に
表示しているのが、クエリなのかフォームなのかがわからないため、
それぞれの場合を回答します。

なお、No.1の補足欄に貼り付けたSQL文を、予め以下の3つに分割して、
細工をしておいてください。
　式【A】：
　　先頭の「Select」から「In (」までを「"」(ダブルクォート)で括ったもの
　　(「"SELECT _単品在庫リアル.～～～自社コード) In ("」)
　式【B】：
　　フォームのテキストボックスを指定する「Forms!～」の式(そのまま)
　　(「[Forms]![在庫所持店舗表作成]![JAN]」)
　式【C】：
　　上記の式の直後の「)) AND」から最後までを「"」で括ったもの
　　　(「")) AND ((_単品在庫リアル.～～～.店舗コード;"」)

【フォームの場合】
上記コマンドボタンのクリック時イベントのVBAまたはマクロに、以下の
ようなコードを追記します：

　<VBAの場合>
　　'当該フォームを開く「DoCmd.OpenForm」の行の後に、以下の
　　'コードを追記
　　Forms!店舗一覧.RecordSource = 【A】 & Me!JAN & 【C】

　<マクロの場合>
　　当該フォームを開く『フォームを開く』アクションの後に、
　　『値の代入』アクションを追加
　　　　・『アイテム』：Forms!店舗一覧.RecordSource
　　　　・『式』：【A】 & 【B】 & 【C】

※一覧表示用のフォーム名を「店舗一覧」と仮定しました。
　 なお、上記コードでレコードソースを設定するので、同フォームの
　 デザインビューでの『レコードソース』は空白にしてしまって下さい。


【クエリの場合】
※こちらの場合、マクロのみでの対応はできません。
上記コマンドボタンで■クエリを開く前に■ 、以下のコードを追記します：

　　'<以下は、変数宣言部分に追記>
　　Dim qdf As DAO.QueryDef

　　'<以下は、クエリを開く前の部分に追記>
　　'フォームで指定したJANコードを、当該クエリの抽出条件に直接記述
　　Set qdf = CurrentDb.QueryDefs("クエリ名")
　　qdf.SQL = 【A】 & Me!JAN & 【B】
　　Set qdf = Nothing


・・・以上です。

No.2 回答者： 30246kiku 回答日時： 2010/10/16 17:56

((_単品在庫リアル.自社コード) In ([Forms]![在庫所持店舗表作成]![JAN]))

の部分を

(Nz(InStr([Forms]![在庫所持店舗表作成]![JAN],_単品在庫リアル.自社コード),0) > 0)

にするとどうなりますか。

この回答への補足

う、うぉ～～～！！！
ううう、うぉ～～～～～！！
複数のデータ抽出できましたーーー！！！！！！！！！！！
感激です。。。；；

ありがとうございます！！
お蔭様で効率よく仕事がこなせそうです＞＜

最後に質問なのですが、単品で出した時に比べ（2、3秒）、
抽出時間が大幅に伸びた（アクセス応答無し表示になる）（3～4分）のは、この関数を使っているからでしょうか？

補足日時：2010/10/17 08:27

No.1 回答者： 30246kiku 回答日時： 2010/10/16 13:15

現在そのクエリをSQLビュー表示した際、WHERE 部分は

WHERE [JAN] In ([Forms]![在庫所持店舗表作成]![JAN])

のようになっているかと思いますが、以下に変更してみてどうなりますか。

WHERE Nz(InStr([Forms]![在庫所持店舗表作成]![JAN],[JAN]),0) > 0

この回答への補足

SELECT _単品在庫リアル.自社コード AS JAN, _単品在庫リアル.店舗コード INTO T1_在庫保持
FROM _単品在庫リアル INNER JOIN _店舗マスタ ON _単品在庫リアル.店舗コード = _店舗マスタ.店舗コード
WHERE (((_単品在庫リアル.自社コード) In ([Forms]![在庫所持店舗表作成]![JAN])) AND ((_単品在庫リアル.理論在庫数量) Not In (0,Null)) AND ((_店舗マスタ.事業部コード)=0))
ORDER BY _単品在庫リアル.自社コード, _単品在庫リアル.店舗コード;

該当クエリのSQLビューとはこのことでしょうか？？
変更する箇所にカッコが多くてどう変えてみればいいのかわかりません。。
お願い致します＞＜

補足日時：2010/10/16 17:29