「Filterプロパティを利用した検索機能」エラーの原因は分かっているのですが、よい回避策が分からずにいます。

Question

前回の続きになります。進捗が遅くてお恥ずかしいですが、どうぞよろしくお願い致します。
Filterプロパティを利用した検索機能についてなのですが、エラーの原因は分かっているのですが、対処の仕方が分からずにいます。どうぞ知恵をおかしください。

検索機能について
ユーザーフォームにオブジェクト（テキストボックスを２つ、検索ボタンを１つ、リストボックスを１つ）を設置しています。

検索にかける文字列は、TextBox1に姓（ex.山田）もしくはｾｲ（ex.ﾔﾏﾀﾞ）、TextBox2に数字４文字（ex.0123）。

検索の参照先は、TextBox1がレコードセット（adoRs）の「姓」もしくは「ｾｲ」のフィールドの値、TextBox2が「電話番号1」もしくは「電話番号2」のフィールドの値です。

検索条件は、TextBox1とTextBox2と共に部分一致です。またテキストボックスへの入力はTextBox1とTextBox2のどちらか一方のみ、もしくは２つとも入力しても検索可能な仕様にしたいと思っています。

最後に検索条件に一致したら、レコードセットを参照して必要な情報をリストボックスに表示します。

エラーがでる場所

adoRs.Filter = "姓 like '%" & Me.TextBox1.Value & "%'" & "OR ｾｲ like '%" & Me.TextBox1.Value & "%'" & _
               "OR 電話番号1 like '%" & Me.TextBox2.Value & "%'" & "OR 電話番号2 like '%" & Me.TextBox2.Value & "%'"

エラーメッセージ
実行時エラー'301':
引数が間違った型、許容範囲外、または競合しています。

原因と思われるもの
Filterプロパティの検索文字列にはNullの値は使用できないという説明がありました。
用意している参照先のAccessデータベースにはNullもあります。
テキストボックスの値は空（""）のときにエラーが出るので、これがNullと競合？しているのだと思われます。

考えてみた解決策
素人考えで解決策を考えてみました。テキストボックスの値が空だとエラーが出るので、空の場合はアンダーバーを入れる、というようにしてみました。しかし、うまいやり方ではないんじゃないかと思われて…。（下記のコードをadoRs.Filterより前にかませてみたら一応動きはしたのですが…）

    If Me.TextBox1.Value = "" Then
        Me.TextBox1.Value = "_"
    Else
        Me.TextBox1.Value = Me.TextBox1.Value
    End If
    If Me.TextBox2.Value = "" Then
        Me.TextBox2.Value = "_"
    Else
        Me.TextBox2.Value = Me.TextBox2.Value
    End If

なにか良い回避策をご教授いただけませんでしょうか？どうぞよろしくお願い致します。
質問に不備がありましたら改善したいと思いますので、ご指摘いただけると幸いです。

全文は下記の通りです。

Option Explicit

'---ACCESS接続用
Private adoCn As Object
Private adoRs As Object
Private strSQL As String

'---ユーザーを検索
Private Sub SearchButton_Click()
    Dim cnt As Long

    Call ConnectDB  'データベースに接続
    
    strSQL = "SELECT * FROM 顧客マスター"
    adoRs.Open strSQL, adoCn
    
    '抽出条件を設定してフィルターをかける
    adoRs.Filter = "姓 like '%" & Me.TextBox1.Value & "%'" & "OR ｾｲ like '%" & Me.TextBox1.Value & "%'" & _
                   "OR 電話番号1 like '%" & Me.TextBox2.Value & "%'" & "OR 電話番号2 like '%" & Me.TextBox2.Value & "%'"
      
    If adoRs.BOF = True And adoRs.EOF = True Then   '条件に一致するデータがない場合、データベースを切断して終了
        Call CutDB
        MsgBox "条件に一致するデータがありません。"
    Else
        With Me.SearchList
            .ColumnCount = 8
            .ColumnWidths = "50;50;40;40;60;200;100;100;"
            .Font.Size = 13
            Do Until adoRs.EOF      'レコードが終了するまで繰り返す
                 .AddItem ""        'リストボックスに表示する
                 .List(cnt, 0) = NullChg(adoRs(3).Value)    '姓
                 .List(cnt, 1) = NullChg(adoRs(4).Value)    '名
                 .List(cnt, 2) = NullChg(adoRs(6).Value)    'ｾｲ
                 .List(cnt, 3) = NullChg(adoRs(7).Value)    'ﾒｲ
                 .List(cnt, 4) = NullChg(adoRs(10).Value)   '都道府県（住所1）
                 .List(cnt, 5) = NullChg(adoRs(11).Value)   '住所2
                 .List(cnt, 6) = NullChg(adoRs(13).Value)   '電話番号1
                 .List(cnt, 7) = NullChg(adoRs(14).Value)   '電話番号2
                adoRs.MoveNext
                cnt = cnt + 1
            Loop
        End With
    End If

    Call CutDB      '検索がすんだら、データベースを切断して終了
    Exit Sub

End Sub

追記

たびたび申し訳ありません。自分がやりたい事というのが自分でもよく把握していなかったみたいです…。今回、皆さま方よりご回答をいただいて、ようやくそれを明確にできてきました。

それで、いただいたご回答を参考にしながら、自分のやりたい抽出条件を書いてみました。
①それぞれのテキストボックスに別々の抽出条件（SearchFilter1とSearchFilter2）を設定する
②その上でテキストボックスへの記入がされているかどうかで条件分岐して
③最初に設定した抽出条件を使用して、最終的な抽出条件（adoRs.filter）を決定する。

Dim SearchFilter1 As String, SearchFilter2 As String

'それぞれのテキストボックスに抽出条件を設定
SearchFilter1 = ("姓 like '%" & Me.TextBox1.Value & "%'" & " OR ｾｲ like '%" & Me.TextBox1.Value & "%'")
SearchFilter2 = ("電話番号1 like '%" & Me.TextBox2.Value & "%'" & " OR 電話番号2 like '%" & Me.TextBox2.Value & "%'")

'それぞれのテキストボックスへの入力の有無により条件分岐
If Me.TextBox1.Value = "" And Me.TextBox2.Value = "" Then       'どちらも未入力の場合
    Call CutDB
    MsgBox "テキストボックスに条件を入力してください。"
    Exit Sub
ElseIf Me.TextBox1.Value <> "" And Me.TextBox2.Value <> "" Then 'どちらにも入力されている場合
    adoRs.filter = SearchFilter1 & " AND " & SearchFilter2
ElseIf Me.TextBox1.Value <> "" And Me.TextBox2.Value = "" Then  'TextBox1のみ入力されている場合
    adoRs.filter = SearchFilter1
ElseIf Me.TextBox1.Value = "" And Me.TextBox2.Value <> "" Then  'TextBox2のみ入力されている場合
    adoRs.filter = SearchFilter2
End If

ただ、これだと adoRs.filter = SearchFilter1 & " AND " & SearchFilter2 でエラーが出てしまいます。
AND を OR にすると動くのですが、意図する仕様は AND です。

エラーメッセージ
実行時エラー'301':
引数が間違った型、許容範囲外、または競合しています。

OR はよくて AND はだめな理由が分かりません。。

追記２

サンプルコードありがとうございます。示していただいたサンプルコードを試してみたのですが、やはり AND のところで、同様のエラーが発生してしまいます。同じように OR だと動きます。

いろいろ試すことで、vbaに少しずつ慣れてきているのは自分でも嬉しいのですが、それは置いといて、なぜ意図どおりに動いてくれないのか…

ORの場合は動くので、最初に思いついた原因の「Nullとうまく参照できていない」というのとは別に原因がありそうだと思い直しています。

追記２-２

公式の説明にANDで結合できないことが書かれてありました。結合の仕方を変える必要があるそうです。

Answer 1

or演算子の前にスペースがありませんけど。

追記

テキストボックスが空の場合には値0の文字列となり、長さ0の文字列とは異なります。
どちらでも""として比較できるようですけど、Nullと合わせてややこしそうなので、
それらを一旦変数に格納してから、処理を行うように変更してみました。
※条件組み立て部分も一部修正

Dim SearchFilter1 As String, SearchFilter2 As String
Dim ConditionsItem1 as string, ConditionsItem2 as string

ConditionsItem1 = IIF(IsNull(Me.TextBox1.Value),"", Me.TextBox1.Value)
ConditionsItem2 = IIF(IsNull(Me.TextBox2.Value),"", Me.TextBox2.Value)

'それぞれのテキストボックスに抽出条件を設定
SearchFilter1 = ("姓 like '%" & ConditionsItem1 & "%'" & " OR ｾｲ like '%" & ConditionsItem1 & "%'")
SearchFilter2 = ("電話番号1 like '%" & ConditionsItem2 & "%'" & " OR 電話番号2 like '%" & ConditionsItem2 & "%'")

'それぞれのテキストボックスへの入力の有無により条件分岐
If ConditionsItem1 = "" And ConditionsItem2 = "" Then       'どちらも未入力の場合
    Call CutDB
    MsgBox "テキストボックスに条件を入力してください。"
    Exit Sub
ElseIf ConditionsItem1 <> "" And ConditionsItem2 <> "" Then 'どちらにも入力されている場合
    adoRs.filter = "(" & SearchFilter1 & ") AND (" & SearchFilter2 & ")"  '←括弧で括る
ElseIf ConditionsItem1 <> "" And ConditionsItem2 = "" Then  'TextBox1のみ入力されている場合
    adoRs.filter = SearchFilter1
ElseIf ConditionsItem1 = "" And ConditionsItem2 <> "" Then  'TextBox2のみ入力されている場合
    adoRs.filter = SearchFilter2
End If

Comments

[miniryu]

ご指摘ありがとうございます。修正させていただきます。

Answer 2

おそらくテキストボックスが空のときは、フィルタの条件が%%になってしまうのが原因ではないでしょうか。
値があるときとないときで条件を動的に変更してみては。

Dim filter As String
filter = ""
If Me.TextBox1.Value <> "" Then
	filter = "姓 like '%" & Me.TextBox1.Value & "%'" & "OR ｾｲ like '%" & Me.TextBox1.Value & "%'"
End If
If Me.TextBox2.Value <> "" Then
	If filter <> "" Then
		filter = filter & " OR "
	End If
    filter = filter & "電話番号1 like '%" & Me.TextBox2.Value & "%'" & "OR 電話番号2 like '%" & Me.TextBox2.Value & "%'"
End If
adoRs.Filter = filter

ちなみに空のときに_で置き換えてしまうと、%_%という条件になってしまい、アンダースコアを含むものが抽出されてしまいますので、間違った処理です。

--
少しだけかっこよく書き直してみました。
但し、条件がOR固定なのでAND条件があったりすると改造が必要です。
単純にOR条件を追加していく分には楽ではないかと思います。

Private Sub SearchButton_Click()
    ～省略～

    Dim filters() As String
    ' テキストボックス1が入力されているとき
    If Me.TextBox1.Value <> "" Then
        Call AddFilter(filters, "姓 like '%" & Me.TextBox1.Value & "%'")
        Call AddFilter(filters, "ｾｲ like '%" & Me.TextBox1.Value & "%'")
    End If
    ' テキストボックス2が入力されているとき
    If Me.TextBox2.Value <> "" Then
        Call AddFilter(filters, "電話番号1 like '%" & Me.TextBox2.Value & "%'")
        Call AddFilter(filters, "電話番号2 like '%" & Me.TextBox2.Value & "%'")
    End If
    '抽出条件を設定してフィルターをかける
    adoRs.Filter = Join(filters, " OR ")

    ～省略～
End Sub

Sub AddFilter(ByRef filters() As String, joken As String)
    If Not Not filters Then
        ReDim Preserve filters(UBound(filters) + 1)
    Else
        ReDim filters(0)
    End If
    filters(UBound(filters)) = joken
End Function

--
ORとANDの混在バージョン。

Private Sub SearchButton_Click()
    ～省略～

    Dim filters() As String
    ' テキストボックス1が入力されているとき
    With Me.TextBox1
        If .Value <> "" Then
            Call AddFilter(filters, "(姓 like '%" & .Value & "%' OR ｾｲ like '%" & .Value & "%')")
        End If
    End With
    ' テキストボックス2が入力されているとき
    With Me.TextBox2
        If .Value <> "" Then
            Call AddFilter(filters, "(電話番号1 like '%" & .Value & "%' OR 電話番号2 like '%" & .Value & "%')")
        End If
    End With
    '抽出条件を設定してフィルターをかける
    adoRs.Filter = Join(filters, " AND ")

    ～省略～
End Sub

Comments

[miniryu]

まだ使ったことがない関数があったりして、こういう書き方もあるのかと、とても勉強になります。
自分の頭が弱いせいか、ここに来てようやく自分のやりたいことが、AND条件を使用するものであることが分かってきました。行きつ戻りつして、お手数をおかけしてしまいすみません。

[ttyp03]

一応ANDにも対応したのを書いて見ました（動作未確認）
参考にしてみてください。

Answer 3

処理を詳細に分けるべきですね。

Dim Filter1 As String
if Me.TextBox1.Value <> "" Then
Filter1 = "姓 like '%" & Me.TextBox1.Value & "%'"
End if

という風に分け、Or が必要なら後から変数に追加し、
adoRs.Filter = Filter1 & Filter2 & Filter3 & Filter4
等としてみては？