ACCESSクエリ抽出条件について

質問

フォーム画面のリストボックスで複数選択が出来るよう設定し、その結果をクエリの抽出条件で
forms![フォーム名].[リストボックス名]
としたのですが全く反映されません。
複数選択が出来ないようにしたら問題なく表示されるのですが、上記の記述以外に入力しないといけないのでしょうか?

回答No.10

O_cyan · ベストアンサー

>(pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]で悩んでいます。
リストボックスのあるフォーム上にリストボックスで選択された条件を吐き出すテキストボックスを作り=[forms]![入力フォーム].[物品分類]ではなくそのテキストボックスの値を代入してはどうですか。
リストボックスをリスト1として吐き出すテキストボックスをText1として
リストボックスの更新後処理に
Private Sub リスト1_AfterUpdate()
Dim Var条件 As Variant
Dim varItm As Variant
Var条件 = Null
For Each varItm In Me.リスト1.ItemsSelected
Var条件 = Var条件 & IIf(Not IsNull(Var条件), " or (pidsyk.bri_cd)=", "") & Me.リスト1.Column(1, varItm)
Next
Me!Text1 = Var条件
End Sub
でText1に複数選択しても吐き出すようにしてWHERE条件の
AND ((pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]))
を
AND ((pidsyk.bri_cd)=[forms]![入力フォーム].[Text1]))
とすれば行けると思います。
Text1はOKなら可視を[いいえ]にして非可視にして見えなくしておけば良いと思いますが。

補足

遅くなりました。確かにいい感じになりました。有り難う御座います。
ただ、一つ選択ではうまくいきますが二つ以上選択した場合は数式に複雑な要素が・・・と出て出来なくなります。
クエリを2行程に分けたり
Var条件 = Var条件 & IIf(Not IsNull(Var条件), " or (pidsyk.bri_cd)=", "")
を
Var条件 = Var条件 & IIf(Not IsNull(Var条件), " or ", "")にしてみたのですがうまくいきません。
クエリに直接1 or 2のように入力すればうまくいくのですが。

回答No.12

noname#22222

s_husky です。

検証結果は、CreateWhere関数は２重の誤りをしていることが判明しました。
第一に、クエリーと関数の関係を理解していないミスであった。
第二に、IN キーワードを使うべきであった。

No.1の回答を不用意に翻したことを反省しお詫びします。

なお、Formロード時にSQL文を作成しレコードソースにセットするように工夫し、CreateWhere関数をINキーワードに書き換えると成功しました。
＊重ね重ねのミス、重々にお詫びしておきます。

補足

別件がありましたので検証が遅くなりすみませんでした。
下の件と同時にしてみましたが
まず、文字列を置き換えてテストしてみたのですが、なりました！！
また、Inキーワードの意味がわかりませんでした。
やはりCreateWhereが未定義関数と表示されます。
モジュールには
Public Function CreateWhere(ByVal frm As String, _
ByVal lst As String, _
ByVal fld As String) As String
Dim I As Integer
Dim N As Integer
Dim strWhere As String
Dim ctl As Control

Set ctl = Forms(frm).Controls(lst)
N = ctl.ListCount - 1
For I = 0 To N
If ctl.Selected(I) Then
If Len(strWhere & "") > 0 Then
strWhere = strWhere & " Or "
End If
strWhere = strWhere & fld & "='" & ctl.ItemData(I) & "'"
End If
Next I
CreateWhere = strWhere >= CreateWhere = "(" & strWhere & ")"
End Function

と登録したのですが。

回答No.11

noname#22222

s_husky です。

No.7 の回答を補足に沿って説明し直します。

＜SQLビューでの手作業確認＞

(pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]

ここを、

(pidsykbri_cd = '物品分類1' Or pidsykbri_cd = '物品分類2')

という文字列に置き換えてテストするということです。
これでOKであれば、次のステップに進みます。

＜CreateWhere関数の組み込み方＞

検索終了日],9,2)) AND ((pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]))　ORDER BY

の部分を

検索終了日],9,2)) AND CreateWhere("入力フォーム","物品分類","pidsyk.bri_cd") ORDER BY

というように変えます。
ただし、この場合、

CreateWhere = strWhere　=> CreateWhere = "(" & strWhere & ")"

と戻り値を工夫する必要がありそうです。

SELECT テーブル1.ID, テーブル1.Item
FROM テーブル1
WHERE CreateWhere("Form1","ListBox","Items");

が動作していますので、問題ないとは思いますが...

※まあ、これだけ複雑だと、何らかの制限に引っかかる可能性もありますから、一応、こちらでも検証してみます。

回答No.9

noname#22222

質問者は、クエリの抽出条件でフォーム名とリストボックス名を指示したら、複数選択されているリストの値に合致するデータを抽出したいみたいですね。ところが、複数選択するとヌル値が返ってくるので困っています。問題は、やはり、クエリの抽出条件でフォーム名、リストボックス名を指定すればデータの抽出に成功することではないでしょうか？
　要点は、当初の目的を達成することです。ただし、フォーム名、リストボックス名だけではWhere節は完成しません。列名も必要ということです。
　フォーム名、リストボックス名、列名さえ判れば、条件文を作成し文字列で戻す関数を作成することが可能です。
　質問者は、「モジュールに登録して下さい」のくだりを読み違えて失敗しているものと推察されます。そこをサポートすれば、成功するものと考えます。

補足

親切にありがとうございます。
フォーム名：入力フォーム
リスト名：物品分類
リストボックスの値集合ソース：SELECT pimbri.bpn_bri_nm, pimbri.bpn_bri_cd FROM pimbri;
連結列：２

クエリは現状SQLビューで
SELECT pidsyk.syk_h, pidsyk.bri_cd, pidsyk.syh_cd, pidsyk.syk_su, IIf(forms!入力フォーム.価格区分リスト="購入価",pidsyk.gen_kn_ka,IIf(forms!入力フォーム.価格区分リスト="薬価/請求価",pidsyk.gen_tei_ka,IIf(forms!入力フォーム.価格区分リスト="マスタ価",pimsyh.gen_kn_ka))) AS 1本包装価格, IIf(forms!入力フォーム.価格区分リスト="購入価",pidsyk.gen_kn_ka/pidsyk.iri_su,IIf(forms!入力フォーム.価格区分リスト="薬価/請求価",pidsyk.gen_tei_ka/pidsyk.iri_su,IIf(forms!入力フォーム.価格区分リスト="マスタ価",pimsyh.gen_kn_ka/pimsyh.iri_su))) AS 1本価格
FROM pidsyk LEFT JOIN pimsyh ON pidsyk.syh_cd = pimsyh.syh_cd
WHERE (((pidsyk.syk_h)>=Mid([forms]![入力フォーム].[検索開始日],1,4) & Mid([forms]![入力フォーム].[検索開始日],6,2) & Mid([forms]![入力フォーム].[検索開始日],9,2) And (pidsyk.syk_h)<=Mid([forms]![入力フォーム].[検索終了日],1,4) & Mid([forms]![入力フォーム].[検索終了日],6,2) & Mid([forms]![入力フォーム].[検索終了日],9,2)) AND ((pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]))
ORDER BY pidsyk.syk_h;

となっています。
このうち(pidsyk.bri_cd)=[forms]![入力フォーム].[物品分類]で悩んでいます。
これでわかっていただけるでしょうか。

回答No.8

O_cyan

>そこで標準か拡張を選択した場合に1行でなく複数行選択出来るようになる方法です
上記補足は分かりますが
リストの複数選択プロパティに標準または拡張を設定するとリストボックスコントロールの値は常にNull値になりますのでforms![フォーム名].[リストボックス名]では取得できません。
なので[リストボックスは選択した値をひとつしか返しませんが複数とは？ちょっと不明ですが。]となりました。

複数選択した場合はItemsSelectedなど使い
Dim Var条件 As Variant
Dim varItm As Variant
strSQL = "Select * From テーブル名" 'SQLを作っておきます
For Each varItm In Me.リストボックス名.ItemsSelected
Var条件 = 'ここにリストボックスで複数選択された値を代入します。
Next
DoCmd.RunSQL strSQL & " WHERE テーブル名.フィールド名 In ( " & Var条件 & " );" '作っておいたSQLに条件を追加して抽出する
こんな感じでやらないと抽出できません。

回答No.7

noname#22222

s_husky です。

・未定義関数と出る---作業をミスしています。
・CreateWhere（）は動作します。

まず、CreateWhere（）の働きを手作業で確認して下さい。
SQLビューでSQL文を表示して

WHERE 列名=値1 OR 列名=値2　・・・・　OR 列名=値N

で、目的の行が抽出されることを確認します。
Nは、リストボックスで選択されている個数です。

CreateWhere関数の作成手順

１、データベースメニューのオブジェクトでモジュールを選択し新規作成をクリック。
２、VBエディターで[挿入]-[プロシージャ]で
CreateWhereと名前、Function、Publicを選択します。以下は、回答を参照して引数、戻り値、関数コードを書いて下さい。

CreateWhere関数は、手作業での条件文の入力を肩代わり関数です。当然に組み込み関数ではありませんので自作しなければなりません。

回答No.6

O_cyan

>リストボックスで複数選択が出来るよう設定し・・
>複数選択が出来ないようにしたら問題なく表示・・
リストボックスは選択した値をひとつしか返しませんが複数とは？ちょっと不明ですが。

>クエリの抽出条件でforms![フォーム名].[リストボックス名]としたのですが全く反映されません。
クエリの抽出条件でforms![フォーム名]![リストボックス名]でもforms![フォーム名].[リストボックス名]でも抽出できるはずです。
抽出条件を記述したフィールドとリストボックスのプロパティのデータにある連結列が合っているか確認してください。
抽出条件を記述したフィールドとリストボックスの連結列が違っていて抽出できないのではないでしょうか。
リストボックスで選択したものがクエリに反映しないのはこれが原因であると思います。その辺を見てください。

補足

リストボックスのプロパティに複数選択ってありますよね。
そこで標準か拡張を選択した場合に1行でなく複数行選択出来るようになる方法です。
しないにすると問題なく表示されますが、下にアドバイスしていただいたとおりの結果だと思います。

回答No.5

noname#22222

s_husky です。
補足を拝見しました。
そこで、再度の補足です。

(1)SQL 文は、クエリーをSQLビューで表示して編集します。
・'Form1'、'ListBox'、'Item'は、実際のフォーム名、リストボックス名、列名に変えて下さい。
・クエリーを軽くするために文字列で参照情報を渡しています。
(2)CreateWhere関数は、モジュールに登録して下さい。

※テスト済みですので動作します。
※Stop文は外して下さい。

補足

早速してみたのですが、未定義関数と表示されてしまうんですが。

回答No.4

noname#22222

CreateWhere関数は、ちょっと敷居が高いのかなと思いました。
そこで、ソースの雛形を示しておきます。

SELECT テーブル1.ID, テーブル1.Item
FROM テーブル1
WHERE CreateWhere("Form1","ListBox","Items");

Public Function CreateWhere(ByVal frm As String, _
　　　　　　　　　　　　　　ByVal lst As String, _
　　　　　　　　　　　　　　ByVal fld As String) As String
　　Dim I　　　　As Integer
　　Dim N　　　　As Integer
　　Dim strWhere As String
　　Dim ctl　　　As Control

　　Set ctl = Forms(frm).Controls(lst)
　　N = ctl.ListCount - 1
　　Stop
　　For I = 0 To N
　　　　If ctl.Selected(I) Then
　　　　　　If Len(strWhere & "") > 0 Then
　　　　　　　　strWhere = strWhere & " Or "
　　　　　　End If
　　　　　　strWhere = strWhere & fld & "='" & ctl.ItemData(I) & "'"
　　　　End If
　　Next I
　　CreateWhere = strWhere
End Function

回答No.3

imogasi

リストボックスに表示するテーブルを
テーブル：生徒
ID 県 学校 学年 性別
1 東京 南小学校 6 男
2 埼玉 北小学校 2 女
3 埼玉 北第２小学校 1 女
4 千葉 中央小学校 3 男
5 神奈川 東小学校 1 男
とする」。
－－－－
生徒フォームをつくり、リストボックスを貼り付ける。
リストボックス：リスト12　(私の場合の名）のプロパティで
値集合タイプ　テーブル
値集合ソース　生徒　(上記テーブル）
列数　　３　　　　　（上記学校フィールドまで３列）
連結列　３　　　　　（上記の３番目・学校フィールドが採られる）
複数選択　標準　　　（複数選択する）
－－－－
コマンドボタンを１つ貼り付け（たとえばキャプションを「検索」）
コマンドボタン：コマンド14 (私の場合の名）
（データをリストボックスから採るきっかけを与えるため設置）
－－－－
コマンドボタンのクリックイベントとして
Private Sub コマンド14_Click()

　Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!生徒
Set ctl = frm!リスト12
For Each varItm In ctl.ItemsSelected
MsgBox ctl.ItemData(varItm)
　Next varItm

End Sub
を作る。
－－－－
フォームで表示－フォームビュー
リストボックスで、たとえば、南小学校、中央小学校の２つの行をクリック
フォームの検索ボタンをクリック
順次、南小学校、中央小学校がメッセージボックスに現れる。
これを変数に保存しておく。
－－－－
これで検索条件が、変数値にはいって、複数決まるから、ＳＱＬ文などに使う。

補足

ありがとうございます。
期待していた内容とは違っていましたが、以前からしたいと思っていたことでしたので逆に非常に感謝しています。そちらの方で使わせてもらいます。

回答No.2

noname#22222

s_husky です。

定かでない部分を確認しました。
次のような関数を作成してテストした結果はＯＫでした。

SELECT テーブル1.ID, テーブル1.Item
FROM テーブル1
WHERE CreateWhere();

CreateWhere()の類を作成して下さい。
要領は先の回答を手掛かりにヘルプ分を参照すればいいでしょう！

補足

早速の返答ありがとうございます。
ただ、SQL初心者な私には理解し難いです。
もう少し詳しく解説していただけないでしょうか。