ACCESS VBA Filterに配列を使いたい

質問

初めての書込みです。誤りがあれば、訂正致しますので、宜しくお願いします。

（背景）
Access2003で作成したプログラムを、3年ほど使ってきました。
少しずつバージョンアップを加え、機能を増やしてきた次第です。
これまでの検索は、クエリによる抽出条件設定を行っていましたが、
入力型フィールドが２つから４つに増えるため、Is Null とlikeによる抽出条件の設定ではカバーしきれないと判断しました。

（本題）
検索結果フォームのレコードに、VBAでFilterをかけるべく頑張ってきましたが、
Loopを使う為にフィールド名を配列変数にしているところで
行き詰まってしまいました。

（やりたいこと）
検索条件　="部署 Like '*人事*'"　　　'としたい。

（今の状態）
検索条件 = "部署 Like '* Form_frm_検索ボックス!部署検索 *'"　'となってしまう。

（１）コード
検索条件 = sh(i) & " Like '*" & "Form_frm_検索ボックス!" & sh(i) & "検索" & "*'"

（２）データ型
Dim 検索条件 As Variant
Dim sh(4) As String
Dim i as integer

コード、及びデータ型で、間違っている場所が分かれば、ご指導頂けないでしょうか。
宜しくお願いします。

回答No.2

DexMachina · ベストアンサー

【要旨】
ご質問のコードを、以下のように修正すれば、ご希望の結果が得られるのでは
ないかと推察します。

<現在>
検索条件 = sh(i) & " Like '*" & "Form_frm_検索ボックス!" & sh(i) & "検索" & "*'"

<修正>
検索条件 = sh(i) & " Like '*" & Form_frm_検索ボックス.Controls(sh(i) & "検索") & "*'"

【説明】
「Form_frm_検索ボックス!○○検索」となる部分をダブルクォーテーションの
外に出し、その値(→ご質問の例では「人事」)をコード上で確定させることで、
「"部署 Like '*人事*'"」という文字列式を生成させています。

このとき、「～!○○検索」の形にするのではなく、当該フォームのControls
コレクションを経由することで、コントロール名(→「○○検索」)による指定を
可能にしているのが『キーポイント』となります。

なお、もしもこのままではわかりにくいようでしたら、「Controls(　)」の括弧の
中に入る部分を「strTB = sh(ｉ) & "検索"」(strTBはString型の変数)とすると、
若干わかりやすいかもしれません。

更に、一旦「varText = Form_frm_検索ボックス.Controls(strTB)」として
「人事」という文字列自体を変数に格納してもOkです。
(varTextはVariant型の変数：Nullの場合、String型では代入できないため：
　又は下記蛇足のように、Nz関数で空文字に変換してString型の変数に代入)

【蛇足】
ご存知だとは思いますが、Nullのレコードは「Like」では抽出できないため、
以下のようなIf文での場合分けが必要となりますので、念のため参考までに：

Dim strText As String

strText = Nz(Form_frm_検索ボックス.Controls(sh(i) & "検索"))
If strText = "" Then
　　'条件の指定なし
　　検索条件 = ""
ElseIf strText = "Null" Then
　　'Nullのレコードを抽出(→「Null」という文字列の指定で対応する場合)
　　検索条件 = sh(i) & " Is Null"
Else
　　'「Null」以外の文字列が指定されている場合
　　検索条件 = sh(i) & " Like '*" & strText & "*'"
End If

お礼

意図を完全にくみ取った回答を頂き、敬服しました。
>>検索条件 = sh(i) & " Like '*" & strText & "*'"
まさに求めていた回答です、ありがとうございました！

Is Nullの条件分岐設定は次に考えようとしていたので、
これまた助かりました、、恥ずかしながら丸コピさせていただきました。

今回フォームでのFilter条件に初トライしてみて、
クエリで組んだ時よりずっと可読性・拡張性に富んでいて
良いな♪と思いました。
ただ…これまでクエリの抽出結果をそのままEXCELへEXPORT、が
できなくなってしまいましたので、今度はそっちに対応しなければ・・・。
（Opn時 Filterなので、クエリをExportすると全件吐き出される・・・）

しばらく考えて、わからないようだったらまたgoo！してみます。
お時間ありましたら、またお付き合い頂ければ幸いです。

※最後に、「急いでいます！」アイコンを選んだにも関わらず、
締めまで時間がかかってしまったこと、お詫び申し上げます。

回答No.4

DexMachina

No.2です。
見て見ぬふりをするのもどうかと思いましたので、補足しておきます。

『Formsコレクション経由の参照』(「Forms!フォーム名」や
「Forms("フォーム名")」)と、『フォームモジュール経由(?)の参照』
(「Form_フォーム名」)は、完全互換や上位互換といった関係には
ありません。

現状で動いている部分までわざわざ修正する場合は、充分に
ご注意下さい。
(前者はVBAの他、プロパティシート上やクエリ上でも使用可能で
　汎用性がある一方、後者ではVBAに限定されるものの、下に
　提示したURLのNo.1でも触れている「インテリセンス」が有効に
　なる(→当該フォーム上のコントロール等も含めて参照可能に
　なる)等の特徴があります)
http://okwave.jp/qa/q8031397.html

なお、Loopについての補足が必要なようでしたら、改めて補足
しますので、その旨追記してください。
(それにしても、折角質問者の方が要点を絞っている状況で、
　なぜ最初から全文を書きたがるのか・・・)

お礼

追加の補足まで頂き、ありがとうございました。
勉強になります！

回答No.3

30246kiku

> Form_frm_検索ボックス!部署検索

上記記述にしているのは、何か意図はありますか？　通常は、
Forms!frm_検索ボックス!部署検索
とか
Forms("frm_検索ボックス").Controls("部署検索")
とかの記述になると思います。

この記述の違いは、（雰囲気）以下の＃１を参照してみてください。

VisibleとOpenFormは意味が同じ？
http://okwave.jp/qa/q7778972.html

「frm_検索ボックス」と「検索結果」の、２つのフォームが存在するという事ですね。
補足をお願いしていいですか？

１）上記記述にしているのは、何か意図はありますか？
２）処理を記述するのは、どちらのフォームになるのでしょうか？
３）各検索条件は AND / OR どちらで繋げれば良いですか？

現状で回答するとすれば、例えば、
・「部署」「部署1」「氏名」「住所」を「frm_検索ボックス」フォームで文字列を作る
・繋げるのは AND

とした場合、以下未検証です（記述するのは「frm_検索ボックス」フォーム）

Public Function GetFilterStr() As String
　　Dim sWhere As String
　　Dim v As Variant
　　Const sAndOr As String = " AND "

　　sWhere = ""
　　For Each v In Array("部署", "部署1", "氏名", "住所")
　　　　With Me(v & "検索")
　　　　　　If (Len(Nz(.Value)) > 0) Then
　　　　　　　　sWhere = sWhere & sAndOr & v & " Like '*" & .Value & "*'"
　　　　　　End If
　　　　End With
　　Next
　　GetFilterStr = Mid(sWhere, Len(sAndOr) + 1)
End Function

※ Is Null の処理は入れてません
※ 上記は AND ～～ AND ～～ と作っておいて、先頭の AND を除いたものを・・・
全部あれば
部署 Like ～ AND 部署1 Like ～ AND 氏名 Like ～ AND 住所 Like ～～　に。

関数 GetFilterStr の戻り値は、Filter に設定する文字列にしたので、

a）「frm_検索ボックス」フォームから「検索結果」フォームに設定する場合

With Forms("検索結果")
　　.Filter = GetFilterStr
　　.FilterOn = Len(.Filter) > 0
End With

b）「検索結果」フォームから「frm_検索ボックス」フォームを参照する場合

Me.Filter = Forms("frm_検索ボックス").GetFilterStr
Me.FilterOn = Len(Me.Filter) > 0

※ 　　For Each v In Array("部署", "部署1", "氏名", "住所")　　部分は、

For Each v In Split("部署,部署1,氏名,住所", ",")　でも良いかも・・・

また、

Dim sh(3) As String

sh(0) = "部署"
sh(1) = "部署1"
sh(2) = "氏名"
sh(3) = "住所"

For Each v In sh

でもできるかも・・・

お礼

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

>１）上記記述にしているのは、何か意図はありますか？
VBA初心者で、マクロから変換したコードをコピペした結果です。
紹介頂いたページ、大変参考になりました、ありがとうございました。

>２）処理を記述するのは、どちらのフォームになるのでしょうか？
検索結果のOPEN時イベントで処理していました。

>３）各検索条件は AND / OR どちらで繋げれば良いですか？
ANDです。

Loopは独学でなんとかなったのですが、
For Each v In Splitは初めてだったので、使用させていただきました！
つたない質問からたくさんの回答を頂き、感謝致します。
また機会がありましたら、宜しくお願いします。

回答No.1

nag0720

sh(i)に"部署"が入っているなら、
検索条件 = sh(i) & " Like '*" & "Form_frm_検索ボックス!" & sh(i) & "検索" & "*'"
とすれば、
検索条件 = "部署 Like '* Form_frm_検索ボックス!部署検索 *'"
となる。

当然といえば当然のことです。

Form_frm_検索ボックスはどんなフォームなのか？　どんなコントロールを配置しているのか？

"Form_frm_検索ボックス!" & sh(i) & "検索"
の"検索"はどういう意味で記述したのか？

それが分からないと回答しようがないでしょう。

お礼

>"Form_frm_検索ボックス!" & sh(i) & "検索"
の"検索"はどういう意味で記述したのか？

補足に至るまで時間がかかってしまい、申し訳ありません。

"検索"は、コントロールの接尾語で、配列関数(sh(i)）のあとに必ずつくキーワードです。
例：部署検索、住所検索、氏名検索　というコントロールたち。。

要領を得ない質問で、申し訳ありませんでした。
回答、ありがとうございました。