ACCESS VBA 複数の条件文

質問

ACCESS超初心者です。
部署にある書籍の一覧表をACCESSのフォームで作成しています。

3つの検索条件を作成したのですが
検索結果を表示させるIf文がVBAでうまく作れません。。。
ご教授ください。

【例】
テーブル名：Tbl_Test
フィールド名:
分野　（テキスト型）
所在（テキスト型）
所有者（テキスト型）

で、テーブルを作り、
検索用フォーム（フォーム名：Frm_Test）に、非連結の
Ctl検索分野　（コンボボックス）
Ctl検索所在（リストボックス）
Ctl検索所有者（リストボックス）
を作成

「Ctl検索所在（リストボックス）」
「Ctl検索所有者（リストボックス）」
それぞれの更新後処理のイベントプロシージャに
以下の6つのパターンの場合
それぞれ別のクエリを表示させるVBAを作成したい

「分野」：未選択
「所在」：未選択
「所有者」：未選択
の場合・・・クエリ「分野絞込なしの所在所有者未選択」を表示

「分野」：未選択
「所在」：未選択
「所有者」：選択
の場合・・・クエリ「分野絞込なしの所有者選択」を表示

「分野」：未選択
「所在」：選択
「所有者」：未選択
の場合・・・クエリ「分野絞込なしの所在選択」を表示

「分野」：選択
「所在」：未選択
「所有者」：未選択
の場合・・・クエリ「分野絞込ありの所在所有者未選択」を表示

「分野」：選択
「所在」：未選択
「所有者」：選択
の場合・・・クエリ「分野絞込ありの所有者選択」を表示

「分野」：選択
「所在」：選択
「所有者」：未選択
の場合・・・クエリ「分野絞込ありの所在選択」を表示

相変わらず説明が下手で申し訳ありません。
初心者の私にとっては難しすぎて
頭がパンクしてしまいました。

よろしくお願いします！

回答No.3

30246kiku · ベストアンサー

> データが連結しているとはどのような意味なのでしょうか？

私は説明べたなので以下を参照してください。
http://oshiete1.goo.ne.jp/qa57126.html

> (2)以下について意味を教えてください

　以下でわかりますでしょうか。

　sTmp = ""
　sTmp = sTmp & OrAnd & "(AAA)"
　sTmp = sTmp & OrAnd & "(BBB)"
　sTmp = sTmp & OrAnd & "(CCC)"

　出来上がった、sTmp の中身は、
　" AND (AAA) AND (BBB) AND (CCC)" となります。

　sTmp = Mid(sTmp, Len(OrAnd) + 1) とすると、
　"(AAA) AND (BBB) AND (CCC)" となります。

　AND ではなく OR に変更する場合、
　　Const OrAnd As String = " AND "
↓
　　Const OrAnd As String = " OR "
　とするだけで、対処できます。

> (3)以下について、

> Select Case Ctl検索分野
> Case 1
> Me.RecordSource = "クエリ01"

　コンボボックスの表示には、パターン番号のようなものを表示しているのでしょうか？

>　　If (Not IsNull(Me.Ctl検索分野)) Then
>　　　　sTmp = sTmp & OrAnd & "([分野]='" & Me.Ctl検索分野 & "')"
>　　End If

　上記での前提として、分野そのものが表示され、連結列になっていた場合のものになります。
　例えば、値集合ソースの内容が
　　SELECT DISTINCT 分野 FROM テーブル名 ORDER BY 分野;

　リストボックスも同様に、リストボックスの値がそのまま抽出条件に使えることを前提としていました。
　また、複数選択：しない　となっていることが前提となっています。

>　　sTmp = ""
>　　If (Not IsNull(Me.Ctl検索分野)) Then
>　　　　sTmp = sTmp & OrAnd & "([分野]='" & Me.Ctl検索分野 & "')"
>　　End If
>　　If (Not IsNull(Me.Ctl検索所在)) Then
>　　　　sTmp = sTmp & OrAnd & "([所在]='" & Me.Ctl検索所在 & "')"
>　　End If
>　　If (Not IsNull(Me.Ctl検索所有者)) Then
>　　　　sTmp = sTmp & OrAnd & "([所有者]='" & Me.Ctl検索所有者 & "')"
>　　End If
>
>　　If (Len(sTmp) > 0) Then
>　　　　sTmp = Mid(sTmp, Len(OrAnd) + 1)
>
>'フォーム自体のレコードソースを変更する場合
>　　　　Me.RecordSource = "SELECT * FROM テーブル WHERE (" & sTmp & ");"
>　　　　Me.Requery

　上記方法では、クエリは使用しません。
　クエリに書いてあるものを、状況に応じて作成し、設定することになります。

　ここで、クエリ自体が不要になるのか考えるところです。
　そのクエリを使ってレポートに、とか、外部出力（ExcelやCSVなど）に使うとか。

>「フォーム自体のレコードソースを変更する場合」
> で自分なりに色々やってみたのですがうまくいきません。。。

　今まで作成したクエリを、デザインビューで開いた後、SQLビュー表示にしてみます。
　その時、参照テーブル名が２つ以上あった場合、以下の部分でテーブル名を明示的に指定します。

　　　　sTmp = sTmp & OrAnd & "([テーブル名].[分野]='" & Me.Ctl検索分野 & "')"
　また、
　　　　Me.RecordSource = "SELECT * FROM テーブル WHERE (" & sTmp & ");"
　の記述は、クエリのSQLビューを参考に、WHERE部分以外まねます。

※　現状考えられている方向を察するに、＃２の方の方法が身近かもしれません。

お礼

丁寧に教えていただいてありがとうございます！
時間はかかりましたがなんとか理解することができました。
また質問することもあると思いますが
よろしくおねがいします！

回答No.2

DexMachina

今回ご質問の件に関しては、No.1の方の回答のように、レコードソースか
フィルタ式をVBA上で直接編集してしまった方が楽なのですが・・・
「頭がパンクしてしまいました」とのことなので、場合分けの仕方の参考
としての回答をします。

Private Sub ReflectSelect()

　　Dim rsl As Integer, strQry As String

　　'IsNull関数により、選択状況を確認(未選択で-1、選択済で0が返される)
　　'※下の「 * 1」は表記形式を揃えるためだけに入れています(=省略可)
　　rsl = - (IsNull(Ctl検索分野) * 1 + IsNull(Ctl検索所在) * 2 + IsNull(Ctl検索所有者) * 4)

　　Select Case rsl　　'全部で8パターン(【追加】の2件は不要?)
　　　　Case 0　　'未選択なし(=-{(0*1)+(0*2)+(0*4)})
　　　　　　strQry = "分野絞込ありの所在所有者選択"　　　'【追加】
　　　　Case 1　　'分野のみ未選択(=-{(-1*1)+(0*2)+(0*4)})
　　　　　　strQry = "分野絞込なしの所在所有者選択"　　　'【追加】
　　　　Case 2　　'所在のみ未選択(=-{(0*1)+(-1*2)+(0*4)})
　　　　　　strQry = "分野絞込ありの所有者選択"
　　　　Case 3　　'分野・所在が未選択(=-{(-1*1)+(-1*2)+(0*4)})
　　　　　　strQry = "分野絞込なしの所有者選択"
　　　　Case 4　　'所有者のみ未選択(=-{(0*1)+(0*2)+(-1*4)})
　　　　　　strQry = "分野絞込ありの所在選択"
　　　　Case 5　　'分野・所有者が未選択(=-{(-1*1)+(0*2)+(-1*4)})
　　　　　　strQry = "分野絞込なしの所在選択"
　　　　Case 6　　'所在・所有者が未選択(=-{(0*1)+(-1*2)+(-1*4)})
　　　　　　strQry = "分野絞込ありの所在所有者未選択"
　　　　Case 7　　'すべて未選択(=-{(-1*1)+(-1*2)+(-1*4)})
　　　　　　strQry = "分野絞込なしの所在所有者未選択"
　　　　Case Else
　　　　　　MsgBox "不明", , "Error"
　　End Select

　　Me.RecordSource = strQry

End Sub

・・・以上、参考まで。
(なお、推奨されるのは、あくまでNo.1の方の方法です)

お礼

ご回答ありがとうございました！
caseの使い方ってこうゆうふうになってるんですね！！
フォームの別の部分でもっと単純なcaseを使用したVBAを
わけもわからずコピペで使っていましたが
今回この回答をいただいたおかげで
理解することができとてもうれしいです！
また質問することもあると思いますが
その際はよろしくお願いします！

回答No.1

30246kiku

実際に動かした検証していないので、雰囲気だけでも

コンボ/リストボックスがクリックされた時に、有効なものだけを使って抽出条件を作ります。
（表示するデータは連結していることが前提です）

Private Sub WhereMakeSet()
　　Dim sTmp As String
　　Const OrAnd As String = " AND "

　　sTmp = ""
　　If (Not IsNull(Me.Ctl検索分野)) Then
　　　　sTmp = sTmp & OrAnd & "([分野]='" & Me.Ctl検索分野 & "')"
　　End If
　　If (Not IsNull(Me.Ctl検索所在)) Then
　　　　sTmp = sTmp & OrAnd & "([所在]='" & Me.Ctl検索所在 & "')"
　　End If
　　If (Not IsNull(Me.Ctl検索所有者)) Then
　　　　sTmp = sTmp & OrAnd & "([所有者]='" & Me.Ctl検索所有者 & "')"
　　End If

　　If (Len(sTmp) > 0) Then
　　　　sTmp = Mid(sTmp, Len(OrAnd) + 1)

'フォーム自体のレコードソースを変更する場合
　　　　Me.RecordSource = "SELECT * FROM テーブル WHERE (" & sTmp & ");"
　　　　Me.Requery

'フォームの Filter 機能を使う場合
　　　　Me.Filter = sTmp
　　　　Me.FilterOn = True

　　Else

'フォーム自体のレコードソースを変更する場合
　　　　Me.RecordSource = "SELECT * FROM テーブル;"
　　　　Me.Requery

'フォームの Filter 機能を使う場合
　　　　Me.FilterOn = False
　　　　Me.Filter = ""
　　End If
End Sub

Private Sub Ctl検索分野_Click()
　　Call WhereMakeSet
End Sub
Private Sub Ctl検索所在_Click()
　　Call WhereMakeSet
End Sub
Private Sub Ctl検索所有者_Click()
　　Call WhereMakeSet
End Sub

お礼

ご回答ありがとうございます！
「フォーム自体のレコードソースを変更する場合」
で自分なりに色々やってみたのですがうまくいきません。。。

こちらかなりの初心者のため理解できない部分が多いせいで
VBAをうまく編集できていないのだと思います。
参考書やネットで色々調べてみたのですが
やっぱり分からないところだらけでした。。
以下の部分について詳細教えていただけると助かります！

(1)初っぱなですが以下の一文
「（表示するデータは連結していることが前提です）」
データが連結しているとはどのような意味なのでしょうか？

(2)以下について意味を教えてください！
（何をしようとしているのかが分かりません。。）

　　・Dim sTmp As String
　　　Const OrAnd As String = " AND "

　　・If (Len(sTmp) > 0) Then
　　　　sTmp = Mid(sTmp, Len(OrAnd) + 1)

(3)以下について、

Private Sub Ctl検索分野_Click()
　　Call WhereMakeSet
End Sub
Private Sub Ctl検索所在_Click()
　　Call WhereMakeSet
End Sub
Private Sub Ctl検索所有者_Click()
　　Call WhereMakeSet
End Sub

現在のところ
コンボボックスについては

Private Sub Ctl検索分野_Click()
Select Case Ctl検索分野
Case 1
Me.RecordSource = "クエリ01"
Case 2
Me.RecordSource = "クエリ02"
・
・
・

リストボックスについては

Private Sub Ctl検索所在_AfterUpdate()
Me.RecordSource = "クエリ03"

Private Sub Ctl検索所有者_AfterUpdate()
Me.RecordSource = "クエリ04"

というように検索結果を表示させるよう
それぞれVBAを作成してあるのですが
それらについてはすべて必要なくなるということなのでしょうか・・？？

そもそも

　　Dim sTmp As String
　　Const OrAnd As String = " AND "

についてまったく理解できていないので
VBAをどのように編集していったらいいかが分かりません。。。

ご教授お願いいたします！