accessのテーブルデータを、VBAだけで「複数条件の検索・抽出」する方法です。
ここでは、フォームにコンボボックスを３つ作成して、複数条件に合致するデータだけを抽出します。また、抽出するデータ型を3種類用意し、記述方法も記載しました。
記事の内容はMicroSoft ACCESS 2013を使用しています
「作成するフォーム」
■使用するテーブルの解説
例として下記のようなテーブルを作成します。
テーブル名は「TJ_生産実績」としました。
また、フォームのコンボボックスで検索する項目は、
「生産日」、
「商品名」、
「ロットNO」、
の３項目とします。
「ID」、「生産数」は表示のみとします。
フィールド名と、データ型、書式設定も確認ください。
■テーブルに入力したデータ
テーブルには下記のようなデータを入れてあります。
「生産日」は日付（S）型で、2018/09/01～04
「商品名」は長いテキスト型で、AAA,ABB,ABC の3種類
「ロットNO」は長整数型で、生産日をyyyymmddにした数値
■フォームの作成とコンボボックスの追加
フォームウィザードで、フォームを新規作成し、コンボボックスを3つ追加しました。
コンボボックスの名前は下記とします。
「生産日cmb」・・・生産日検索用
「商品名cmb」・・・商品名検索用
「ロットNOcmb」・・・ロットNO検索用
■コンボボックスの値集合ソース設定
各コンボボックスの値集合ソースクエリを示します。
データは「TJ_生産実績」テーブルを元に、項目をグループ化して作成します。
なお、「生産日」と「ロットNO」は最新日付、NOが上に来るように降順で並べ替えしています。
「生産日cmb」
「商品名cmb」
「ロットNOcmb」
■あとはフォームのVBAコードを書き込むだけ
各コンボボックスの「更新後処理」（AfterUpdate）には、「kensaku」というSubプロシージャを呼び出します。
---フォームのVBAコード全文---
Option Compare Database
Private Sub ロットNOcmb_AfterUpdate()
Call kensaku
End Sub
Private Sub 商品名cmb_AfterUpdate()
Call kensaku
End Sub
Private Sub 生産日cmb_AfterUpdate()
Call kensaku
End Sub
Public Sub kensaku()
Dim strwork As String
strwork = ""
If Me.生産日cmb = "" Or IsNull(Me.生産日cmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "生産日 =" & "#" & Me.生産日cmb & "# "
End If
If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "商品名 =" & "'" & Me.商品名cmb & "' "
End If
If Me.ロットNOcmb = "" Or IsNull(Me.ロットNOcmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "ロットNO =" & Me.ロットNOcmb & " "
End If
If strwork = "" Then
Me.RecordSource = "SELECT * FROM TJ_生産実績 ORDER BY ID"
Else
Me.RecordSource = "SELECT * FROM TJ_生産実績 Where " & strwork & "ORDER BY ID"
End If
End Sub
---コードここまで---------------------------------
■解説① コンボボックス
まずは前半のフォームで、コンボボックスの値を選択・指定した場合の更新後処理内容をみてみます。
Call kensaku・・・ここではkensakuというプロシージャを呼び出して終了です。
「Call」は省略可能ですが、わかりやすいように記述してあるだけです。
'「ロットNOcmbの更新後処理」
Private Sub ロットNOcmb_AfterUpdate()
Call kensaku
End Sub
'「商品名cmbの更新後処理」
Private Sub 商品名cmb_AfterUpdate()
Call kensaku
End Sub
'「生産日cmbの更新後処理」
Private Sub 生産日cmb_AfterUpdate()
Call kensaku
End Sub
■解説② 検索パターン
kensakuプロシージャでの3つのデータ型の検索パターンについて。
'パターン① 日付型の抽出
'コンボボックスが空白、Ｎullの時は何もしない。
'strworkの内容が空値でない場合、AND+空白を代入します。
'ただし最初の検索項目のみAND追加の行は無くてもかまいません。
'strworkに日付の検索条件を代入します。
'このとき日付データの前後に#を入れます。また、後ろの#の後には空白があります。
If Me.生産日cmb = "" Or IsNull(Me.生産日cmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "生産日 =" & "#" & Me.生産日cmb & "# "
End If
'パターン② テキスト型の抽出
'コンボボックスが空白、Ｎullの時は何もしない。
'strworkの内容が空値でない場合、AND+空白を代入します。
'strworkにテキストデータの検索条件を代入します。
'このときテキストデータの前後に'を入れます。また、後ろの'の後には空白があります。
If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "商品名 =" & "'" & Me.商品名cmb & "' "
End If
'パターン③ 数値型の抽出
'コンボボックスが空白、Ｎullの時は何もしない。
'strworkの内容が空値でない場合、AND+空白を代入します。
'strworkにテキストデータの検索条件を代入します。
'数値型の抽出は、数値のみでOKです。ただし、やはり行の最後には空白を入れておきます。
If Me.ロットNOcmb = "" Or IsNull(Me.ロットNOcmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "ロットNO =" & Me.ロットNOcmb & " "
End If
■解説③ レコードソースの変更
最後にフォームのレコードソースを変更します。
検索パターンで作成した内容にてクエリ(SQL)を作成し、レコードソースに引当てします。
'全てのコンボボックスにデータがない場合は、テーブルデータをそのまま全て表示する。
'IDで昇順並べ替えをしています。
'コンボボックスに一つでもデータがある場合は、抽出条件strworkの検索結果を表示する。
'IDで昇順並べ替えをしています。
If strwork = "" Then
Me.RecordSource = "SELECT * FROM TJ_生産実績 ORDER BY ID"
Else
Me.RecordSource = "SELECT * FROM TJ_生産実績 Where " & strwork & "ORDER BY ID"
End If
■解説のおまけ あいまい検索について
例えば"C"という文字がつく商品名を全て抽出したいなどの場合、下記となります。
あいまい検索に使用するLikeの後に'*テキストデータ*'+空白を入れます。
If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then
Else
If strwork <> "" Then strwork = strwork & "AND "
strwork = strwork & "商品名 Like '" & "*" & Me.商品名cmb & "*' "
End If
■最後に
クエリで検索する場合、項目が多いと、クエリの抽出条件がこんなことになりますね。
ちょっと見にくくて申し訳無いですが。
これは、5つの項目について抽出条件を作成したものですが、行・列ともにかなり多くなります。さらに検索項目を後で増やすなどの修正が入ると、もう、面倒でしょうがないです。なので、特に条件項目が多い場合や、後で増えることが予想されるときは、VBAにしておくと便利なのです。
データ型が大体、上記の3パターンが多いので、ひな形として置いておきます。そしてパターン①②③のいずれかをコピーして、項目名を変更すれば良いだけとなります。（あいまい検索もよく使用しています）
クエリで検索は面倒だなと思ったら、これで検索の実装がかなり楽になると思うのでやってみてはいかがでしょうか。
ExcelVBAでAccessを操作するツボとコツがゼッタイにわかる本
- 作者: 立山秀利
- 出版社/メーカー: 秀和システム
- 発売日: 2015/11/25
- メディア: 単行本
- この商品を含むブログを見る