SQL文 Where句の複数検索

質問

先日教えて頂いたところから自分なりに複数検索をする方法を考えて書いてみたのですが、これだとテキストボックスに複数検索条件を入力するとSQLの構文エラーになってしまいうまくいきません。
先日教えて頂いた方、見ていたらまたアドバイスを頂けたら助かります。

Private Sub コマンド11_Click()

'変数の設定
Dim myDB As DAO.Database
Dim myRS As Recordset
Dim strCnd As String
Dim strWhr As String
Dim strSQL As String

'定数の宣言
Const conSQL As String = "Select * From T_障害票マスタ"

'　テキストボックスの値を確認
'　Nz関数を使用して、テキストとして扱える空文字に変換
strCnd = Nz(Me!テキスト1, "") '←◆コントロールの値を参照◆

'「テキスト1」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
'条件指定時は曖昧検索
strWhr = strWhr & " " & strWhi & " 起票日 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト3, "") '←◆コントロールの値を参照◆

'「テキスト3」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
'条件指定時は曖昧検索
strWhr = strWhr & " And 依頼者 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト5, "") '←◆コントロールの値を参照◆

'「テキスト5」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
'条件指定時は曖昧検索
strWhr = strWhr & " And 部署 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト7, "") '←◆コントロールの値を参照◆

'「テキスト7」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
'条件指定時は曖昧検索
strWhr = strWhr & "And 社員番号 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト9, "") '←◆コントロールの値を参照◆

'「テキスト9」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
'条件指定時は曖昧検索
strWhr = strWhr & "And 対象システム Like '*" & strCnd & "*'"
End If

'SQL文を作成
strSQL = conSQL & vbCrLf & strWhr

'自身のレコードソースに設定
Me.RecordSource = strSQL

'検索結果の表示
DoCmd.OpenForm ("データ修正画面")
Forms("データ修正画面").RecordSource = strSQL

回答No.3

nda23 · ベストアンサー

>「パラメータの入力」という～
「依頼者」というフィールドがDB（あるいはクエリ）上に存在しない
からです。テーブル（あるいはクエリ）設計を確認してください。

前にも回答したように修正すべきです。

If strCnd <> "" Then
strWhr = strWhr & " AND 起票日 = '" & strCnd & "'"
End If
　　　　　　　　　　　↓
If strCnd <> "" Then
　　If strWhr <> "" Then strWhr = strWhr & " AND "
　　strWhr = strWhr & "起票日=#" & strCnd & "#"
End If

If strCnd <> "" Then
strWhr = strWhr & "And 社員番号 = '" & strCnd & "'"
End If
　　　　　　　　　　　↓
If strCnd <> "" Then
　　If strWhr <> "" Then strWhr = strWhr & " AND "
　　strWhr = strWhr & "社員番号=" & strCnd
End If

If strCnd <> "" Then
strWhr = strWhr & " And 部署 Like '*" & strCnd & "*'"
End If
　　　　　　　　　　　↓
If strCnd <> "" Then
　　If strWhr <> "" Then strWhr = strWhr & " AND "
　　strWhr = strWhr & "部署 Like '*" & _
　　Replace(strCnd, "'", "''") & "*'"
End If
★依頼者、対象システム　も同じ

If (Len(strWhr) > 0) Then strWhr = " WHERE " & Mid(strWhr, 6)
　　　　　　　　　　　↓
If strWhr <> "" Then strWhr = " WHERE " & strWhr

DoCmd.OpenForm ("データ修正画面")★不要

お礼

ご回答ありがとうございます。
まさにそのとおりでした。
テーブルのフィールドを確認したら存在しないフィールドを指定してました。
本当に助かりました。
ありがとうございます。
また何かありましたらよろしくお願いします。

回答No.2

nda23

幾つか問題点があります。
(1)起票日
これは日付型ではないですか？日付型では以下のような書式です。
起票日=#2009/11/4#　←　文字列は'で囲み、日付は#で囲む。
また、Like演算子は使えません。
(2)" And 依頼者 Like
最初の条件かも知れないのに、いきなり"And"はマズイでしょう。
常に、前に条件が有るか無いかを確認して、"And"の要否を決める
必要があります。
(3)社員番号 Like '*"
社員番号が文字型なら、これでも良いのですが、数値ですと、形式が
一致しないのでエラーになります。また、Like演算子は使えません。
(4) Like '*" & strCnd & "*'"
指定文字列の中に'（アポストロフィ）が含まれると、文字列が終端
してしまい、残りの文字が意味不明になってしまいます。
strCnd = Replace(strCnd, "'", "''") このようにして、'は二重化
しておく必要があります。
(5)その他
DoCmd.OpenForm ("データ修正画面")　これは構文エラーでは？

Me.RecordSource = strSQL
Forms("データ修正画面").RecordSource = strSQL

意味が分かりません。普通、帳票フォームのフォームヘッダ等に
条件指定テキストボックスを設けて、自身のRecordSourceを変更
するか、単票フォームにサブフォームを埋め込み、サブフォームのRecordSourceを変更するかのいずれかが多く用いられる手法です。
まぁ、無いこともないでうか・・・

以上の点に注意して、プログラムしてみてください。

お礼

詳しい説明ありがとうございます。
アドバイスを元に下記のようにしたんですが、
依頼者を入力して実行すると「パラメータの入力」というメッセージBOXが出てきてしまい再度依頼者を入力する形になります。
それを未入力のまま「OK」を押すと今度は検索情報が表示されなくなります。
これはどういうことなのでしょうか？
よろしければ教えてください。

'　テキストボックスの値を確認
'　Nz関数を使用して、テキストとして扱える空文字に変換
strCnd = Nz(Me!テキスト1, "") '←◆コントロールの値を参照◆

'「テキスト1」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
strWhr = strWhr & " AND 起票日 = '" & strCnd & "'"
End If

strCnd = Nz(Me!テキスト3, "") '←◆コントロールの値を参照◆

'「テキスト3」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
strWhr = strWhr & " AND 依頼者 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト5, "") '←◆コントロールの値を参照◆

'「テキスト5」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
strWhr = strWhr & " And 部署 Like '*" & strCnd & "*'"
End If

strCnd = Nz(Me!テキスト7, "") '←◆コントロールの値を参照◆

'「テキスト7」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
strWhr = strWhr & "And 社員番号 = '" & strCnd & "'"
End If

strCnd = Nz(Me!テキスト9, "") '←◆コントロールの値を参照◆

'「テキスト9」の入力値を元に、Where条件句を作成
If strCnd <> "" Then
strWhr = strWhr & "AND 対象システム Like '*" & strCnd & "*'"
End If

If (Len(strWhr) > 0) Then strWhr = " WHERE " & Mid(strWhr, 6)
'SQL文を作成
strSQL = conSQL & vbCrLf & strWhr
Debug.Print strSQL
'自身のレコードソースに設定
Forms("データ修正画面").RecordSource = strSQ
'検索結果の表示
DoCmd.OpenForm ("データ修正画面")

回答No.1

30246kiku

はじめまして（だと思います）

私の書き方が参考になれば

抽出条件が全て AND 接続であることから以下の様に書きます。

　　Dim sWhere As String

　　sWhere = ""
　　If (Len(Nz(Me.txt1)) > 0) Then
　　　　sWhere = sWhere & " AND XXX1 Like '*" & Me.txt1 & "*'" ' ☆
　　End If
　　If (Len(Nz(Me.txt2)) > 0) Then
　　　　sWhere = sWhere & " AND XXX2 Like '*" & Me.txt2 & "*'"
　　End If
　　If (Len(Nz(Me.txt3)) > 0) Then
　　　　sWhere = sWhere & " AND XXX3 Like '*" & Me.txt3 & "*'"
　　End If
　　If (Len(sWhere) > 0) Then sWhere = " WHERE " & Mid(sWhere, 6) '★

テキストボックス txt1 ～ txt3 をチェックして、文字があれば条件を生成します。

みそは、sWhere に条件を作っていきますが、１つ目の生成（☆）でも、先頭に AND を付加します。
これは、どの条件で生成されていても　★　部分で、先頭の AND を削除した形で
WHERE を作成するためのものになります。
（ AND ・・・・・・ AND ・・・・・・・　を　WHERE ・・・・・・ AND ・・・・・・・ へ）

で、strSQL = conSQL & sWhere　として完成になります。

これを踏まえて、ご質問の

> '「テキスト1」の入力値を元に、Where条件句を作成
> If strCnd <> "" Then
> '条件指定時は曖昧検索
> strWhr = strWhr & " " & strWhi & " 起票日 Like '*" & strCnd & "*'"
> End If

strWhi が何者か？わかりません。
"WHERE" であるのなら、If でスキップしたら "WHERE" は存在しないことになります。
後を見ても "WHERE" を付けているところは見当たりません。

strSQL = conSQL & vbCrLf & strWhr
の後にでも、
Debug.Print strSQL
として、どのような文字列が生成されているか確認した方が良いと思います。
（もしくは、MsgBox で strSQL を表示させるとか）

strSQL を２か所の RecordSource に設定しているようですが、
何をされたいのかわからないので、ここについてはコメントできません。

※VBAへのコメントは、処理を読むのに邪魔にならない程度がいいと思います。
'条件指定時は曖昧検索
とかは要らないと思います。（逆に、あると読みにくいと思います）
・・・・Like '*" & strCnd & "*'"
を見ればわかるので、、

お礼

詳しいご回答ありがとうございます。
アドバイスのおかげでとりあえずSQL構文のエラーは回避できましたがまだわからないことがあるのでよかったらまた教えてください。