Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2016)
複数ワードで3つのフィールドのOR検索
投稿日時: 18/03/27 16:47:52
投稿者: hana_brenda

いつもありがとうございます。access vba初心者です。
検索フォームにコンボボックス、テキストボックスがいくつかあります。
コンボボックスで条件を指定したうえで、txt検索ワードに入力したキーワードで3つのフィールド(工事種目・名称・形状)でOR検索するところまではできたのですが、このtxt検索ワードに複数の単語を入力して(あるいはテキストボックスを2つか3つ作って)検索できるようにするにはどうすればよろしいでしょうか?
 
Private Sub cmd検索_Click()
strCriteria = ""
 With Me
  
    If Nz([cbo工事名選択]) Then
         strCriteria = strCriteria & " AND [工事名ID] = " & [cbo工事名選択]
    End If
   ----------省略----------
        
      If Nz(![txt課事務所], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([課事務所] Like '*" & ![txt課事務所] & "*')"
     End If
   
  'txt検索ワードに入力した単語を工事種目、名称、形状でor検索する
     If Nz(![txt検索ワード], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード] & "*' OR " & "[名称] Like '*" & ![txt検索ワード] & "*' OR " & "[形状] Like '*" & ![txt検索ワード] & "*')"
     End If

     '先頭の " And " を取り除く
    strCriteria = Mid(strCriteria, 6)
   
     '変数 strCriteria の値を
    'イミディエイトウィンドウに出力
    Debug.Print strCriteria
   
     With ![T_単価表検索のサブフォーム].Form
         .Filter = strCriteria
         .FilterOn = (strCriteria <> "")
     End With
   
 End With
 
 '------------------------------
 ' フィルタをかけた結果が0件だったらメッセージボックスを表示する
If T_単価表検索のサブフォーム.Form.Recordset.RecordCount = 0 Then
 MsgBox "検索条件に該当するデータは見つかりませんでした。"
End If
 '------------------------------
 End Sub
 
宜しくお願い致します。

回答
投稿日時: 18/03/27 19:28:21
投稿者: Suzu

Creteria を複数にするだけですよ。
 
txt検索ワード1、txt検索ワード2、txt検索ワード3 があるとすれば
 
 
strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード1] & "*' OR " & "[名称] Like '*" & ![txt検索ワード1] & "*' OR " & "[形状] Like '*" & ![txt検索ワード1] & "*')"
 
strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード2] & "*' OR " & "[名称] Like '*" & ![txt検索ワード2] & "*' OR " & "[形状] Like '*" & ![txt検索ワード2] & "*')"
 
strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード3] & "*' OR " & "[名称] Like '*" & ![txt検索ワード3] & "*' OR " & "[形状] Like '*" & ![txt検索ワード3] & "*')"
 
となります。
 
もちろん、txt検索ワード1 txt検索ワード2 txt検索ワード3 全てのワードが含まれいるのを探しています。
いずれか なのであれば、txt検索ワード2 txt検索ワード3 の部分の頭の And を Or に変えます。
 
 

引用:
このtxt検索ワードに複数の単語を入力して

こちらであれば、Split関数をの 区切り文字 を 複数単語の区切り文字を一致させれば
区切り文字で分けた文字を配列として取得できますから、あとは先の手法と一緒です。

回答
投稿日時: 18/03/27 20:16:05
投稿者: hatena
投稿者のウェブサイトに移動

hana_brenda さんの引用:
このtxt検索ワードに複数の単語を入力して

 
下記が参考になるかと。
 
AccessでWEB検索のように曖昧検索したい - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-96.html
 
Replace関数とBuildCriteria関数を使うことでシンプルに記述できます。

投稿日時: 18/03/28 11:21:49
投稿者: hana_brenda

Suzu様、hatena様ありがとうございます。
 
まずは、テキストボックスを6個にして、OR検索、AND検索両方可能にしてみました。
AND検索は問題ないのですが、OR検索の2つ目3つ目のワード(txt検索ワード5,6)を入力すると、
その前に指定してあるコンボボックス、テキストボックスの値が反映されなくなってしまいます。
 
Private Sub cmd検索_Click()
 
strCriteria = ""
   
 With Me
  
    If Nz([cbo工事名選択]) Then
         strCriteria = strCriteria & " AND [工事名ID] = " & [cbo工事名選択]
    End If
     
     If Nz([cbo県市町村選択]) Then
         strCriteria = strCriteria & " AND [県市町村ID] = " & [cbo県市町村選択]
    End If
     
     If Nz([cbo課事務所選択]) Then
         strCriteria = strCriteria & " AND [事務所ID] = " & [cbo課事務所選択]
    End If
   
    If Nz([cbo案件名選択]) Then
         strCriteria = strCriteria & " AND [案件名ID] = " & [cbo案件名選択]
    End If
     
     If Nz(![txt案件名], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([案件名] Like '*" & ![txt案件名] & "*')"
     End If
         
     If Nz(![txt備考], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([備 考] Like '*" & ![txt備考] & "*')"
     End If
   
     If Nz(![txt単価コード], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([単価コード] Like '*" & ![txt単価コード] & "*')"
     End If
     
  'txt検索ワード1,2,3に入力した単語を工事種目、名称、形状でor検索する
     
    If Nz(![txt検索ワード1], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード1] & "*' OR " & "[名称] Like '*" & ![txt検索ワード1] & "*' OR " & "[形状] Like '*" & ![txt検索ワード1] & "*')"
     End If
   
   If Nz(![txt検索ワード2], "") <> "" Then
         strCriteria = strCriteria & _
                       " OR ([工事種目] Like '*" & ![txt検索ワード2] & "*' OR " & "[名称] Like '*" & ![txt検索ワード2] & "*' OR " & "[形状] Like '*" & ![txt検索ワード2] & "*')"
     End If
   
    If Nz(![txt検索ワード3], "") <> "" Then
         strCriteria = strCriteria & _
                       " OR ([工事種目] Like '*" & ![txt検索ワード3] & "*' OR " & "[名称] Like '*" & ![txt検索ワード3] & "*' OR " & "[形状] Like '*" & ![txt検索ワード3] & "*')"
     End If

  'txt検索ワード4,5,6に入力した単語を工事種目、名称、形状でOR検索し、txt検索ワード4,5,6はAND検索する
   If Nz(![txt検索ワード4], "") <> "" Then
         strCriteria = strCriteria & _
                       " And ([工事種目] Like '*" & ![txt検索ワード4] & "*' OR " & "[名称] Like '*" & ![txt検索ワード4] & "*' OR " & "[形状] Like '*" & ![txt検索ワード4] & "*')"
     End If
   
   If Nz(![txt検索ワード5], "") <> "" Then
         strCriteria = strCriteria & _
                       " AND ([工事種目] Like '*" & ![txt検索ワード5] & "*' OR " & "[名称] Like '*" & ![txt検索ワード5] & "*' OR " & "[形状] Like '*" & ![txt検索ワード5] & "*')"
     End If
   
    If Nz(![txt検索ワード6], "") <> "" Then
         strCriteria = strCriteria & _
                       " AND ([工事種目] Like '*" & ![txt検索ワード6] & "*' OR " & "[名称] Like '*" & ![txt検索ワード6] & "*' OR " & "[形状] Like '*" & ![txt検索ワード6] & "*')"
     End If

     '先頭の " And " を取り除く
    strCriteria = Mid(strCriteria, 6)
   
     '変数 strCriteria の値を
    'イミディエイトウィンドウに出力
    Debug.Print strCriteria
   
     With ![T_単価表検索のサブフォーム].Form
         .Filter = strCriteria
         .FilterOn = (strCriteria <> "")
     End With
   End With
 
 '------------------------------
 ' フィルタをかけた結果が0件だったらメッセージボックスを表示する
 
If T_単価表検索のサブフォーム.Form.Recordset.RecordCount = 0 Then
 MsgBox "検索条件に該当するデータは見つかりませんでした。"
End If
 '------------------------------
 End Sub

回答
投稿日時: 18/03/28 12:46:29
投稿者: Suzu

Debug.Print strCriteria
 
で、デバックウィンドにCreteriaの文字列を吐き出しているのですから、
クエリのWHERE句に、その文字列を指定し、クエリのデザインビューに変えたらどこに問題があるのか
判り易くなるのでは?

投稿日時: 18/03/28 16:18:45
投稿者: hana_brenda

Suzu様早速の返信ありがとうございます。
 
デバックウィンドウで、コンボボックスで事務所を選択後、txt検索ワード1のみ入力したときは、
[事務所ID] = 12 And ([工事種目] Like '*シーリング*' OR [名称] Like '*シーリング*' OR [形状] Like '*シーリング*')
と事務所で絞り込まれておりますが、txt検索ワード2も入力すると、
([工事種目] Like '*シーリング*' OR [名称] Like '*シーリング*' OR [形状] Like '*シーリング*') OR ([工事種目] Like '*撤去*' OR [名称] Like '*撤去*' OR [形状] Like '*撤去*')
とコンボボックスが無視されていました。
 
クエリだと、条件を指定してSQLビューにすると
WHERE (((T_単価表検索.工事種目) Like "*シーリング*") AND ((T_単価表検索.事務所ID)=5)) OR (((T_単価表検索.名称) Like "*シーリング*") AND ((T_単価表検索.事務所ID)=5)) OR (((T_単価表検索.形状) Like "*シーリング*") AND ((T_単価表検索.事務所ID)=5)) OR (((T_単価表検索.工事種目) Like "*撤去*") AND ((T_単価表検索.事務所ID)=5)) OR (((T_単価表検索.名称) Like "*撤去*") AND ((T_単価表検索.事務所ID)=5)) OR (((T_単価表検索.形状) Like "*撤去*") AND ((T_単価表検索.事務所ID)=5));
となりましたが、これをVBAにすることができません。勉強不足ですみません。

回答
投稿日時: 18/03/28 17:37:27
投稿者: Suzu

引用:
コンボボックスが無視されていました。

 
シングルステップにて実行して行き、strCreteria の内容を確認しましょう。
どこかで、頭の部分が消されているのではないですか?

回答
投稿日時: 18/03/29 11:44:44
投稿者: Suzu

すみません。ちょっと言い方が悪かったです。
 
コード自体に問題は無さそうです。
 
コンボボックスの入力忘れ等も含め、確認して頂く為に、シングルステップにて実行しましょう。
と言う表記にしています。 strCreteria の内容をシングルステップ毎に確認するのが目的では無いです。
 
 
可能性としては、
Filter だったか、Creteria(Docmd.OpenForm等の引数)の文字数に上限があった記憶があります。
WEB検索をしても記事がみつかりません。
ただ、文字数で上限があるはずなので、こんな綺麗に分節で切れるのかな。と言うのが正直な所です。
 
 
SQLの構築について、
LIKE の部分はINDEXが使用できない検索になります。
 
ですので、
 
[工事種目] Like '*シーリング*'
OR
[名称] Like '*シーリング*'
OR
[形状] Like '*シーリング*'
 
は、
([工事種目] & [名称] & [形状]) Like '*シーリング*'
でも良いと思いますよ。
  ※テストしてみてください。
 
 

引用:
これをVBAにすることができません。勉強不足ですみません。

提示のコードは質問者さんが作成しているのではないのですが?

回答
投稿日時: 18/03/29 11:47:15
投稿者: Suzu

追伸
まず、目先の問題を解決してからの話しですが、現在のコードでは、目的の抽出が行えないとおもいます。
目先の部分を解決してからとも思いましたが、先のコンボボックスの条件が反映されていない部分にも繋がるので・・
 
・・・気付いたのは、コンボボックス群の入力した条件は、各テキストボックスに掛かるのか。
と言う部分。
 
 
例えば
 
[工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3
 ※数値は適当です。
 
条件として、
[工事名ID]、[県市町村ID]、[事務所ID] は並列のAND条件です。
 
問題は、テキストボックスの部分を含めると strCreteria は
 
[工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3
And [工事種目] Like '*い*' OR [名称] Like '*い*' OR [形状] Like '*ろ*'
 
こうなっています。
 
これは、
[工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3 And [工事種目] Like '*い*'
OR [名称] Like '*い*'
OR [形状] Like '*い*'
 
です。
 
つまりコンボボックスの選択は、工事種目 のみと、同列の条件となっています。
 
名称または、形状に 【い】 が 含まれる部分は、工事ID、県市町村ID、事務所ID
に関わらずに抽出されます。
 
多分、
[工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3
And ([工事種目] Like '*い*' OR [名称] Like '*い*' OR [形状] Like '*ろ*')
この様に括弧が必要なのでは?
って事です。

回答
投稿日時: 18/03/29 14:43:11
投稿者: Suzu

訂正です。
 
[工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3
And
([工事種目] Like '*い*' OR [名称] Like '*い*' OR [形状] Like '**')
 
    ↓
 
([工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3)
And
([工事種目] Like '*い*' OR [名称] Like '*い*' OR [形状] Like '*い*')
 
或いは
 
([工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3 And [工事種目] Like '*い*')
OR
([工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3 And [名称] Like '*い*')
OR
([工事名ID] = 1 AND [県市町村ID] = 2 AND [事務所ID] = 3 And [形状] Like '*い*')

投稿日時: 18/03/29 17:14:00
投稿者: hana_brenda

Suzu様、おっしゃる通りです。
コンボボックスの選択は、txt検索ワード1には([工事種目][名称][形状]に)かかっていますが、txt検索ワード2とtxt検索ワード3にはかかっていませんでした。
括弧が必要だな、ということまでは分かったのですが、VBAでどのように括弧をつければよいのかわからず悩んでいました。
 
コードは検索して調べながら変更を加えて書き換えたので一から書いたわけではありません。すみません。
パラメータークエリでデータベースを作るつもりでいたのですが、ユーザーの要求に答えることが難しく、VBAを勉強し始めたばかりです。

回答
投稿日時: 18/03/30 09:46:30
投稿者: sk

引用:
txt検索ワード1,2,3に入力した単語を工事種目、名称、形状でor検索する

引用:
txt検索ワード4,5,6に入力した単語を工事種目、名称、形状でOR検索し、txt検索ワード4,5,6はAND検索する

Private Sub cmd検索_Click()
  
    Dim strCriteria As String
    Dim strSubCriteria As String
  
    strCriteria = ""
     
    With Me
     
        If Nz([cbo工事名選択]) Then
            strCriteria = strCriteria & " AND ([工事名ID] = " & [cbo工事名選択] & ")"
        End If
         
        If Nz([cbo県市町村選択]) Then
            strCriteria = strCriteria & " AND ([県市町村ID] = " & [cbo県市町村選択] & ")"
        End If
         
        If Nz([cbo課事務所選択]) Then
            strCriteria = strCriteria & " AND ([事務所ID] = " & [cbo課事務所選択] & ")"
        End If
         
        If Nz([cbo案件名選択]) Then
            strCriteria = strCriteria & " AND ([案件名ID] = " & [cbo案件名選択] & ")"
        End If
         
        If Nz(![txt案件名], "") <> "" Then
            strCriteria = strCriteria & _
                          " And ([案件名] Like '*" & ![txt案件名] & "*')"
        End If
         
        If Nz(![txt備考], "") <> "" Then
            strCriteria = strCriteria & _
                          " And ([備 考] Like '*" & ![txt備考] & "*')"
        End If
         
        If Nz(![txt単価コード], "") <> "" Then
            strCriteria = strCriteria & _
                          " And ([単価コード] Like '*" & ![txt単価コード] & "*')"
        End If
         
        'txt検索ワード1,2,3に入力した単語を工事種目、名称、形状でor検索する
         
        strSubCriteria = ""
         
        If Nz(![txt検索ワード1], "") <> "" Then
            strSubCriteria = strSubCriteria & _
                             " OR ([工事種目] Like '*" & ![txt検索ワード1] & "*' OR " & "[名称] Like '*" & ![txt検索ワード1] & "*' OR " & "[形状] Like '*" & ![txt検索ワード1] & "*')"
        End If
         
        If Nz(![txt検索ワード2], "") <> "" Then
            strSubCriteria = strSubCriteria & _
                             " OR ([工事種目] Like '*" & ![txt検索ワード2] & "*' OR " & "[名称] Like '*" & ![txt検索ワード2] & "*' OR " & "[形状] Like '*" & ![txt検索ワード2] & "*')"
        End If
         
        If Nz(![txt検索ワード3], "") <> "" Then
            strSubCriteria = strSubCriteria & _
                             " OR ([工事種目] Like '*" & ![txt検索ワード3] & "*' OR " & "[名称] Like '*" & ![txt検索ワード3] & "*' OR " & "[形状] Like '*" & ![txt検索ワード3] & "*')"
        End If
         
        If strSubCriteria <> "" Then
            '先頭の " OR " を取り除く
            strSubCriteria = Mid(strSubCriteria, 5)
            'strSubCriteria に追記された複合 OR 条件全体を AND 条件として追加
            strCriteria = strCriteria & _
                          " AND (" & strSubCriteria & ")"
        End If
         
         
        'txt検索ワード4,5,6に入力した単語を工事種目、名称、形状でOR検索し、txt検索ワード4,5,6はAND検索する
        If Nz(![txt検索ワード4], "") <> "" Then
            strCriteria = strCriteria & _
                          " And ([工事種目] Like '*" & ![txt検索ワード4] & "*' OR " & "[名称] Like '*" & ![txt検索ワード4] & "*' OR " & "[形状] Like '*" & ![txt検索ワード4] & "*')"
        End If
         
        If Nz(![txt検索ワード5], "") <> "" Then
            strCriteria = strCriteria & _
                          " AND ([工事種目] Like '*" & ![txt検索ワード5] & "*' OR " & "[名称] Like '*" & ![txt検索ワード5] & "*' OR " & "[形状] Like '*" & ![txt検索ワード5] & "*')"
        End If
         
        If Nz(![txt検索ワード6], "") <> "" Then
            strCriteria = strCriteria & _
                          " AND ([工事種目] Like '*" & ![txt検索ワード6] & "*' OR " & "[名称] Like '*" & ![txt検索ワード6] & "*' OR " & "[形状] Like '*" & ![txt検索ワード6] & "*')"
        End If
         
        '先頭の " And " を取り除く
        strCriteria = Mid(strCriteria, 6)
         
        '変数 strCriteria の値を
        'イミディエイトウィンドウに出力
        Debug.Print strCriteria
         
        With ![T_単価表検索のサブフォーム].Form
            .Filter = strCriteria
            .FilterOn = (strCriteria <> "")
        End With
    End With
     
    '------------------------------
    ' フィルタをかけた結果が0件だったらメッセージボックスを表示する
     
    If T_単価表検索のサブフォーム.Form.Recordset.RecordCount = 0 Then
        MsgBox "検索条件に該当するデータは見つかりませんでした。"
    End If
    '------------------------------
 
End Sub
 
引用:
([工事種目] & [名称] & [形状]) Like '*シーリング*'
でも良いと思いますよ。

例えば[名称]の値が "ディズニーシー" であり、
かつ[形状]の値が "リング" であるようなケースでも
ヒットしてしまうので、あまりお奨めはしません。
(せめて何らかの区切り文字を挿入した方がよい)

投稿日時: 18/03/30 16:24:29
投稿者: hana_brenda

sk様、ありがとうございます。
 
変数がstrCriteriaだけでは足りないのかな?と思ったのですが、その先どうすればよいのかまでは分かりませんでした。
こうしてstrSubCriteriaにOR検索をまとめてから括弧でくくり、ANDをかければよいのですね?
 
基礎的なことで申し訳ないのですが、
 '先頭の " OR " を取り除く
            strSubCriteria = Mid(strSubCriteria, 5)
 
先頭のANDやORを取り除く意味と、MID関数の使い方が理解できないまま使っていました。
Mid(strSubCriteria, 5) は、strSubCriteriaの5文字目以降を取り出すのではないのですか?

回答
投稿日時: 18/03/30 16:35:31
投稿者: sk

引用:
先頭のANDやORを取り除く意味と、MID関数の使い方が理解できないまま使っていました。
Mid(strSubCriteria, 5) は、strSubCriteriaの5文字目以降を取り出すのではないのですか?

ここでの Mid 関数の直接の意味はそうです。
その目的が「 strSubCriteria の最初の条件式の前に付加された
" OR " を取り除いた文字列を取り出す」ことなので、
目的に即したコメントを追加したに過ぎません。

投稿日時: 18/04/04 15:17:01
投稿者: hana_brenda

Suzu様、hatena様、sk様 ありがとうございました。