Access VBA:半角スペースで区切られた検索文字列をLike演算子を使ったAND検索条件(Where句)に変換するユーザー定義関数

2016.02.19

Access VBA:半角スペースで区切られた検索文字列をLike演算子を使ったAND検索条件(Where句)に変換するユーザー定義関数

単語と単語を半角スペースで区切った文字列を受け取ってLike演算子(前方/後方一致)を使った検索条件のWhere句に変換するユーザー定義関数をAccessのVBAで作成したので覚書きしておきます。

例えば、"A B"という文字列を引数として渡すと"[フィールド名] like '%A%' AND [フィールド] like '%B%'"というWhere句を作成するユーザー定義関数です。

スポンサーリンク

作成するVBAのユーザー定義関数の考え方

作成するユーザー定義関数の処理内容を以下に記載します

  1. 引数として半角スペースで区切られた文字列と検索対象のフィールド名を受け取る
  2. 受け取った文字列を綺麗にする(データクレンジング)
  3. 受け取った文字列の前後に不要なスペースがないか、区切りが全角スペースになっていないか、半角スペースが無駄に連続していないかをチェックして該当する場合は削除や変換などをしてデータのクレンジングを行う
  4. Like演算子を使用したAND条件のWhere句に変換する
  5. 変換したWhere句を関数の返り値として渡す

ユーザー定義関数のサンプルプログラム

作成するユーザー定義関数のサンプルプログラムを以下に記載します。

Function CreateSQL(strSearch As String, strFldName As String)
'前後の不要なスペースを削除
strSearch = Trim(strSearch)
'全角スペースを半角スペースに変換
strSearch = StrConv(strSearch, vbNarrow)
'連続したスペースを削除
Do Until InStr(strSearch, "  ") = 0
    strSearch = Replace(strSearch, "  ", " ")
Loop
strSearch = Replace(strSearch, " ", "%' AND [" & strFldName & "] like '%")
strSearch = " Where [" & strFldName & "] like '%" & strSearch & "%'"
CreateSQL = strSearch
End Function

ユーザー定義関数の中で行っているデータのクレンジングについて補足で説明しておきます。

前後の不要なスペースを削除

例えば、" A B "など、前後に不要なスペースがある場合、スペースを検索文字列の区切りという前提でプログラムの処理を行っているので検索文字列の区切り一以外に半角スペースが入っていると処理結果に不都合が出てしまいます。

それを防ぐためにTrim関数を使用して前後のスペースを削除(トリミング)しています。

全角スペースを半角スペースに変換

半角スペースを検索文字列として扱うので全角スペースは検索文字列の区切りとは認識されません。

日本語の検索文字列を指定した際にスペースを全角設定しているユーザーはそのまま区切りとして全角スペースを入力する可能性があるので、それを考慮した処理です。

連続したスペースを削除

検索文字列の区切りとして使う半角スペースが連続して入力されていた場合を考慮しています。

半角スペースが連続して入力されていても半角スペース1つに変換します。

例えば、"A__B___C"とかいう入力になっていても"A_B_C"に変換します。

※説明上分かりづらいので半角スペースを"_"で表現しています。

作成したユーザー定義関数を使用したVBAのサンプルプログラム

作成したAccessのVBAのサンプルプログラムは、ユーザーから入力された検索文字列をWhere句に変換してSQLを実行し、結果をデバッグウィンドウに表示するサンプルプログラムです。

ADO(Microsoft ActiveX Data Objects)を使用していますので、プログラムの実行にはADOの参照設定が必要です。

以下にサンプルプログラムのコードを記載します。

Sub Example01()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset
Dim strSQL As String, strSearch As String
strSearch = InputBox("検索する文字列をを入力してください。" _
        & vbCrLf & "半角スペースで区切るとAND条件になります。")
If Len(strSearch) = 0 Then Exit Sub
strSearch = CreateSQL(strSearch, "メモ")
strSQL = "SELECT * FROM TEST" & strSearch
Set con = CurrentProject.Connection
rec.Open strSQL, con
  Do Until rec.EOF
    Debug.Print rec("メモ")
    rec.MoveNext
  Loop
End Sub

サンプルプログラム補足

ADOをLike演算子を使用する際のワイルドカード文字は、通常のAccessのクエリーでLike演算子を使用する場合と異なり"*"ではなく、"%"が使われますので注意してください。

Access VBA:ADOでLike演算子を使用する場合のワイルドカード文字は"＊"ではなく"％"を使用する
AccessのVBAでADO(Microsoft ActiveX Data Objects)を使用してLike演算子を使用したSQL文を実行してレコードセットを取得す場合、通常のクエリーでは前方一致や後方一致に"*"アスタリスクを使用します...
selifelog.com
2016.02.19

記事まとめ

紹介したサンプルプログラムではInputBoxを使って検索文字列の入力をユーザーに求めるようになっていますが、実際に使用する場面としてはフォームで使用しています。

この記事でフォームを使ったサンプルを紹介すると長くなってしまうので、フォームを使用したサンプルは別の記事に書く予定です。

コメント

タイトルとURLをコピーしました