MS Access Tips/Sample and VBA and Blog customize etc...

複数条件の抽出フォームの設計 その1

タイトル画像

フォーム上に複数のフィールドに対する条件を入力するテキストボックスをそれぞれ配置して、その条件で抽出したい。条件未入力の場合は無視したい。

これもよく見かけるFAQに近い物です。このような要望を実現するフォームの設計例を紹介します。。シンプルな例から入り、汎用的なものへ拡張していく予定です。今回はその第1弾です。

難易度:

サンプル仕様

サンプルフォーム外観
FrmFilter_1.png

対象フィールド

フィールド名 データ型 抽出仕様
社員コード 数値型 不等号、Between And 等の演算子入力可
フリガナ テキスト型 部分一致
氏名 テキスト型 部分一致
誕生日 日付/時刻型 2つのテキストボックスで範囲指定
プロフィール メモ型 空白区切りで複数キーワード入力可

帳票フォームのフォームヘッダーに下記のテキストボックスを配置

txt社員コード、txtフリガナ、txt氏名、min誕生日、max誕生日、txtプロフィール

さらに下記のコマンドボタンを配置。
cmdFilter(標題: 抽出)、cmfFilterOff(標題: 抽出解除)

フォームデザインビュー
フォームデザインビュー

フォームモジュール

2010/12/10追記: コードに間違いがありましたので修正しました。
25行目
誤) strFilter = strFilter & " AND 氏名 Like *" & Me.txt氏名 & "*"
正) strFilter = strFilter & " AND 氏名 Like '*" & Me.txt氏名 & "*'"
2013/11/16追記: コードにバグがありましたので修正しました。
38行目
誤) "*" & Replace(Me.txtプロフィール, " ", "* And *") & "*")
正) "*" & Replace(StrConv(Me.txtプロフィール, vbWide), " ", "* And *") & "*")
2014/08/27追記: コードにバグがありましたので修正しました。
29行目、33行目の 生年月日 を 誕生日 に修正

条件入力ボックスが未入力(Null)で無ければ、条件文字列変数(strFilter)に条件を追加していきます。先頭に余分な" And "が付きますので最後でMid関数で削除してます。

社員コードの 演算子 を含む条件は、BuildCriteria関数を利用してます。BuildCriteria関数はフォームに独自の検索機能を実装するでも使用してますので参照ください。

プロフィールの空白区切りで複数キーワード入力できる仕様は、AccessでWEB検索のように曖昧検索したい で紹介した方法を利用してますので、ご参照ください。

補足

このサンプルでは、フォームを開いた直後は全件表示されていますが、1件も表示されていない状態で開きたいという場合は、「開くとき」のイベントプロシージャで下記のように1件も抽出されないようなフィルタを設定します。

また、抽出結果を別フォームで開きたい場合は、上記のコードのハイライト部分(41~46行)を下記のように変更します。

サンプルファイルが下記からダウンロードできます。
FrmFilter_1_07.zip (Access 2007-2010 形式 - 32kb)
FrmFilter_1.zip (Access 2002-2003 形式 - 28kb)
FrmFilter_1_2k.zip (Access 2000 形式 - 28kb)

拍手する

30 Comments

iccya says..."検索でエラー"

お世話になります。
検索フォームで調べていて 貴殿HPにたどり着きました。
改造していますが、もう少しでできそうです
少し教えてください。
サンプルMDB(FrmFilter_1.zip )をDLして F社員フォームの
氏名欄に”青木”と入れて抽出したところ
==
実行時エラー’2448’:
このオブジェクトに値を代入することはできません。
==
とエラーになります。
デバックをしますと
Me.Filter = Mid(strFilter, 5)
の部分が黄色く反転されます。
なにがわるいのでしょうか?

MS ACCESS 2003 使用です。
よろしくお願い致します

2010.07.28 16:22 | URL | #mQop/nM. [edit]
hatena says..."Re:検索でエラー"

iccyaさん、遅い返事ですみません。

コードに記述間違いがありました。

記事を修正しておきましたのでご参考ください。

2010.12.07 03:09 | URL | #5uE6dEgY [edit]
ey says..."初心者です。"

とてもお世話になっております。
件数の多い一覧表を複数の検索条件で絞り込みをしたくて、web検索をしていたところ貴殿HPにたどりつきました。
とても綺麗な書き方で初心者の私にもすごく分かりやすいです。

ここを見ながら自分のデータベースのフォームを作成していたのですが、どうしてもわからなくて質問させていただいたしだいです。
ぶしつけなのは承知ですので、無視してくださってもかまいません。

元になるクエリの日付を

月: Format([日付],"yyyy-mm")

年 月 で表示させているのですが、それをtxt月というテキストボックスに入力し、cmbfilterボタンを押せば任意の月が出てくるようにするにはどう書けばいいのでしょうか。

どうか宜しくお願いします。

2012.03.27 17:55 | URL | #- [edit]
hatena says..."re: 初心者です。"

月フィールドを利用するなら下記のようなコードを挿入すればいいでしょう。

If Not IsNull(Me.txt月) Then
  strFilter = strFilter & " AND 月='" & Me.txt月 & "'")
End If

件数が多くて、処理が重いようでしたら、日付フィールドにインデックスを設定して、
下記のコードにするといいでしょう。

If Not IsNull(Me.txt月) Then
  strFilter = strFilter & _
  " AND 日付 >= #" & Me.txt月 & "# AND 日付< #" & DateAdd("m",1,Me.txt月) & "#")
End If

  

2012.03.27 22:19 | URL | #5uE6dEgY [edit]
ey says..."御礼が遅くなり大変失礼を致しました。"

仕事場ではインターネットがつながらないので、今日お返事をいただいているのを見ることができました。せっかくすぐに返信をいただいていたのに大変失礼をいたしました。
ほんとうにありがとうございます!さっそく取り組んでみたいと思います。いきなりの質問に答えていただいて本当に感謝しております。これからもここで色々勉強させていただきたいと思います。ほんとうにありがとうございました。

2012.03.31 22:57 | URL | #- [edit]
k.k says...""

いつもお世話になっております。
検索結果をデータシートビューで固定させるにはどうしたらよろしいのでしょうか?
検索までは普通にできるのですが、そこがどうしても変更できません。
どうかご教授いただけますようよろしくお願い致します。

2012.04.16 10:38 | URL | #HxJJe2bw [edit]
hatena says..."re: k.kさん"

「検索結果をデータシートビューで固定させる」とは具体的にどのようなことでしょうか。

もう少し詳細に説明してください。

2012.04.16 10:55 | URL | #5uE6dEgY [edit]
k.k says...""

検索ボタン押した後の表示が単票表示となり、ビューを一度クリックしないとデータシートになりません。
そのためデータシートで検索結果を表示したいと考えております。
よろしくお願いします。

2012.04.17 17:58 | URL | #- [edit]
hatena says..."re:k.kさん"

結果をデータシート表示にしたいのですね。

では、対象テーブルからデータシート表示のフォームを作成して、
記事中の補足の別フォームで開く方法でデータシート表示のフォームを開けばいいでしょう。

条件を入力するフォームは、非連結にしておきます。

2012.04.19 16:25 | URL | #5uE6dEgY [edit]
ey says..."この間はありがとうございました"

この間は複数条件の月ごとの抽出を教えて頂き希望どおりの抽出フォームを作ることができました。本当にありがとうございました。

また壁にぶつかってしまい、あつかましくメールさせていただきました。
 やりたいことは、"月報フォーム"で月を指定して抽出し、10個のクエリをエクセルの1つのbookにそれぞれのクエリ名のシートに出力したいのです。”月: Month([日付])”に”[Forms]![月報フォーム]![cob_月]” で抽出しています。クエリは選択クエリとクロス集計クエリです。ネットで検索して見よう見まねでcodeを書いてみたのですが、うまくいきません。1回目は出力できても、違う月に変更して出力しようとすると、エラーがでます。ネットをさまよいかれこれ2週間以上悩んでいます。どうかご教授いただければ嬉しいです。宜しくお願いいたします。

2012.05.15 01:02 | URL | #- [edit]
hatena says..."re:この間はありがとうございました"

もう少し具体的に状況を説明してもわないと、回答は難しいですね。

また、ここのコメント欄では、このブログ記事についての質問、意見を交換する場所ですので、Access関係の掲示板で、質問された方がよろしいかと思います。

2012.05.23 14:18 | URL | #5uE6dEgY [edit]
すますま says..."複数テーブルの同時検索について"

お世話になります。
いつも参考にさせて頂いており、初心者の私もわかりやすく、
大変助かっております。
突然で恐縮ですが、一点教えて頂けますでしょうか。

上記、テーブルの「社員」を検索していると思いますが、複数のテーブルを同時に検索したいのですが、コードはどう書けばいいでしょうか。

上記を例に具体的に言いますと、

<テーブル>
「社員_本社」「社員_大阪支店」「社員_名古屋支店」の3つから同時に検索。

<検索項目>
上記の通り、社員コード、フリガナ、氏名、誕生日、プロフィールの複数検索。

<検索結果>
上記の通り、「F_社員」の検索結果欄に、検索した複数のテーブルのレコードを表示。

以上、お手数ですがよろしくお願い致します。

2013.01.22 11:06 | URL | #- [edit]
hatena says..."re:複数テーブルの同時検索について"

いまさらの遅いレスで申し訳ありません。見てくれるといいのですが。

「F_社員」のレコードソースを3つのテーブルを下記のようなユニオンクエリで結合したものにすればいいでしょう。

SELECT "本社" AS 店名, * FROM 社員_本社
UNION ALL
SELECT "大阪支店" AS 店名, * FROM 社員_大阪支店
UNION ALL
SELECT "名古屋支店" AS 店名, * FROM 社員_名古屋支店;

ただし、ユニオンクエリにすると更新はできなくなりますので閲覧専用になります。

そもそも、社員テーブルを店ごとに3つに分けるという設計自体がデータベースとしては間違いです。
現在のテーブルに店名フィールドを追加して一つのテーブルにまとめておけばユニオンクエリを使う必要もないし、編集も可能になります。

また店ごとの一覧が必要な場合は、クエリで抽出すればいいだけです。

2013.04.07 15:23 | URL | #5uE6dEgY [edit]
kano says..."別フォームのチェックボックスを抽出条件にしたい"

いつもお世話になっております。
kanoと申します。
投稿日より大分経っており、申し訳ございませんが、
ご指導いただけると幸いです。

上記の記事を参考に抽出フォームを作成し、
別記事(http://hatenachips.blog34.fc2.com/blog-entry-299.html
の都道府県抽出用のフォームと合わせた抽出を行いたいと思っているのですが、
どのように抽出条件とすればよいのでしょうか。

当方、Accessを使い始めて5日目の超初心者なもので、
初歩的かつ失礼な質問かとは思いますが、
よろしくお願いいたします。

2013.09.13 13:43 | URL | #eKems4dw [edit]
hatena says..."re:別フォームのチェックボックスを抽出条件にしたい"

抽出フォームのレコードソースのクエリに、テーブル「T_都道府県抽出用」を追加して、都道府県CD フィールドで結合します。
選択 フィールドの抽出女権を True に設定します。

以上です。

2013.09.13 15:35 | URL | #5uE6dEgY [edit]
itsuki says..."実行時エラー2431と"

はじめまして。かなりお世話になっております。
上記のサンプルファイルからダウンロードし
プロフィールに , や ( を使用するとエラーになってしまいます。どうにか調べてやってみたところうまくいきません。
何か方法はございますでしょうか。
どうかよろしくお願いします。

2013.11.15 23:17 | URL | #- [edit]
itsuki says..."もうしわけございません"

つい先ほど質問したitsukiです。
タイトル欄に関して実行時エラー2431とかきましたが
まったくのまちがいでした。
正しくは2423(半角括弧を使用時のエラー)と2432(,を使用時のエラー)でした。申し訳ございません。

2013.11.15 23:21 | URL | #- [edit]
hatena says..."実行時エラー2431"

半角の (), などを使うと、BuildCriteria関数でエラーになるようです。

コードの37行目の

      strFilter = strFilter & " AND " & BuildCriteria("プロフィール", dbText, _
      "*" & Replace(Me.txtプロフィール, " ", "* And *") & "*")

を下記に修正してください。

      strFilter = strFilter & " AND " & BuildCriteria("プロフィール", dbText, _
      "*" & Replace(StrConv(Me.txtプロフィール, vbWide), " ", "* And *") & "*")

StrConv関数で全角に変換して対処しました。

2013.11.16 01:18 | URL | #5uE6dEgY [edit]
itsuki says..."実行時エラー2431"

教えてもらったとおりやりましたら問題なく無事に動きました。
ありがとうございました。

2013.11.16 21:13 | URL | #- [edit]
かなへー says..."パラメーターとMid(strFilter, 5)"

こんにちは。
フォームの作成で検索していたところ、こちのページにたどり着き、勉強させていただいています。
まだ始めたばかりでよくわかっていないところもありますが、質問させてください。

申し訳ないのですが、2点質問させてください。
1つは、ここでZIPファイル(2007-2010形式)をダウンロードさせていただきました。
誕生日のみ入力し、抽出ボタンをしたところ、「パラメーター入力 生年月日」と表示されました。
どのようにしたら回避できますでしょうか?

もう1つは、補足のところで、以下のように記載がありますが、Mid(strFilter, 5)の意味を教えていただけないでしょうか?数字の部分が特にわかりませんでした。
また、抽出結果を別フォームで開きたい場合は、上記のコードのハイライト部分(41~46行)を下記のように変更します。
DoCmd.OpenForm "フォーム名", , , Mid(strFilter, 5)

よろしくお願いします。

2014.08.27 22:59 | URL | #nIWdzhM2 [edit]
hatena says..."re:パラメーターとMid(strFilter, 5)"

> 1つは、ここでZIPファイル(2007-2010形式)をダウンロードさせていただきました。
> 誕生日のみ入力し、抽出ボタンをしたところ、「パラメーター入力 生年月日」と表示されました。
> どのようにしたら回避できますでしょうか?

コードの 29行目、33行目の 生年月日 を 誕生日 に修正してください。

記事のコードは修正しました。サンプルファイルも修正したものに差し替えました。

> もう1つは、補足のところで、以下のように記載がありますが、Mid(strFilter, 5)の意味を教えていただけないでしょうか?数字の部分が特にわかりませんでした。

複数の条件式をつなげるために、式の前に AND を付けています。
最終的には strFilter には

AND 条件式1 AND 条件式2 AND 条件式3

というような文字列が入りますが、先頭の「 AND 」が不要になりますので、それを削除するためのコードです。

2014.08.27 23:46 | URL | #5uE6dEgY [edit]
かなへー says..."re:re:パラメーターとMid(strFilter, 5)"

こんにちは(^^)

ありがとうございます!
理解できました。

とても勉強になります。
また参考にさせてください。

2014.08.28 12:52 | URL | #nIWdzhM2 [edit]
中山 says..."複数条件抽出したものを印刷したいです"

いつもホームページで勉強させていただいております。

複数の条件抽出をフォーム上で表示することは出来たのですが、そのデータを印刷するレポートの作成方法が分かりません。

レポートを開くボタンに
上記のコードの下に
DoCmd.OpenReport "R顧客一覧", acViewPreview, , strFilter

を入れたのですが、

『実行時エラー '3075':

クエリ式'AND 顧客名 Like '*○○*"の構文エラー : 演算子がありません。'』というエラーが表示されレポートが開きません。

色々試しましたがどうにも開けないのでお問い合わせしました。

2014.11.17 12:56 | URL | #P7C.QHBI [edit]
みのわ says..."サンプルのダウンロード"

初めまして最近ACCESSの勉強を始めました。
検索画面を作成したく探していたところこちらのサイトにたどり着きました。
自分がやりたい事がそのまんまでここしかないと思いました。

ところが、自分で作成してみたところ検索結果を表示する場所の設定が分からなくて、サブフォームを作成してみたのですがうまく表示してくれません。

サンプルをダウンロードし、自分の物と比較しようと思ったのですがダウンロードができませんでした。

ご面倒とは思いますが送って頂く事は可能でしょうか?
よろしくお願いします。

2016.07.14 03:31 | URL | #- [edit]
hatena says..."re:サンプルのダウンロード"

サンプルファイルですが、現在、ファイル置き場のサーバー障害でダウンロードできなくなっています。復旧見込みが度々延長されているので、復旧の見込みはないかも知れません。
現在、別のファイル置き場を検討中ですのでしばらくお待ち下さい。

検索結果の表示ですが、このページのものは、
帳票フォームを作成して、フォームヘッダーに抽出条件を入力するテキストボックス等を配置します。
結果は自身のフォームに表示しています。
その為、自分自身のFilterプロパティに条件を設定しています。

 Me.Filter = Mid(strFilter, 6)
 If strFilter = "" Then
  Me.FilterOn = False
 Else
  Me.FilterOn = True
 End If

もし、サブフォームを配置してそこに結果を表示するなら、
Me の部分を、Me.サブフォームコントロール名.Form に変更すればOKです。

 Me.サブフォームコントロール名.Form.Filter = Mid(strFilter, 6)
 If strFilter = "" Then
  Me.サブフォームコントロール名.Form.FilterOn = False
 Else
  Me.サブフォームコントロール名.Form.FilterOn = True
 End If

2016.07.14 09:10 | URL | #5uE6dEgY [edit]
みのわ says..."サブコントロール名"

迅速なお返事ありがとうございます!

早速Me.部分を変更したのですが「メソッドまたはデータメンバーが見つかりません。」とコンパイルエラーが表示されます。
サブコントロール名の部分が青くなりますので、サブコントロール名が間違っているという事でしょうか?

初歩的な質問になってしまいますが、サブコントロール名が記載してある場所を教えて頂けたらと思います。

Webで検索してみたのですが、サブフォーム名とサブコントロール名は別という事までは理解したのですが、確認の方法がみつかりませんでした。

ご教授頂けたら幸いです。

2016.07.14 17:18 | URL | #- [edit]
みのわ says..."できました!!"

すみません、できました!!

メインフォームとサブフォームの二つのフォームを関連付けていませんでした。関連付けたらちゃんと作動してくれました!

ありがとうございました!!

引き続きサイトにて勉強させて頂きますのでよろしくお願いします。

2016.07.15 01:52 | URL | #- [edit]
mito says..."or条件にした場合"

こちらのサイトを拝見させて頂いて、大変勉強になりました。
and条件については、希望の通りの抽出ができるようになりましたが、そこにor条件も追加して使用できるようにしたいと思い、プロフィール部分のコードのReplace(StrConv(Me.txtプロフィール, vbWide), " ", "* And *") & "*")のANDをORにかえて、複数キーワードと他の項目にキーワードを入れたところ、思うような抽出結果が得られませんでした。
プロフィール1and氏名 プラス プロフィール2のような結果になります。
(プロフィール1or2)and氏名のような結果にするにはどのように書いたらよいか、ご教示頂けませんでしょうか。

どうぞよろしくお願いいたします。

2016.08.10 11:46 | URL | #- [edit]
hatena says..."re:or条件にした場合"

> and条件については、希望の通りの抽出ができるようになりましたが、そこにor条件も追加して使用できるようにしたいと思い、プロフィール部分のコードのReplace(StrConv(Me.txtプロフィール, vbWide), " ", "* And *") & "*")のANDをORにかえて、複数キーワードと他の項目にキーワードを入れたところ、思うような抽出結果が得られませんでした。
> プロフィール1and氏名 プラス プロフィール2のような結果になります。
> (プロフィール1or2)and氏名のような結果にするにはどのように書いたらよいか、ご教示頂けませんでしょうか。

or と and が混在する場合、and が優先順位が高いので、お気づきのように、or条件は () で囲む必要があります。

strFilter = strFilter & " AND " & BuildCriteria("プロフィール", dbText, _
"(*" & Replace(StrConv(Me.txtプロフィール, vbWide), " ", "* OR *") & "*)")

2016.08.10 14:07 | URL | #5uE6dEgY [edit]
mito says..."or条件できました"

教えて頂いた通りのコードで、抽出できました!
括弧でくくるのはなんとなく分かったのですが、どこに入れても構文エラーで上手く出来なかったので、教えて頂いてとても助かりました。これからも色々と参考にさせて頂いて勉強します。本当にありがとうございました。

2016.08.10 15:17 | URL | #- [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/129-15a0cf40
該当の記事は見つかりませんでした。