AccessVBA【SQL】Like演算子であいまい検索してレコード抽出

AccessVBA開発

Sub set_○△□品番inSTOCK()

'ストックにある品番で、○△□に含まれる品番を抽出

'→ T_○△□品番 作成

Call clear_table("T_○△□品番")

Dim strSQL As String, str As String

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsS As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rsS = New ADODB.Recordset

strSQL = ""
strSQL = strSQL & " SELECT"
strSQL = strSQL & " tb1.品番 AS 品番"
strSQL = strSQL & " ,tb2.○△□ AS ○△□"
strSQL = strSQL & " FROM"
strSQL = strSQL & " M_○△□ AS tb1"
strSQL = strSQL & " INNER JOIN"
strSQL = strSQL & " T_○△□ AS tb2"
strSQL = strSQL & " ON"
strSQL = strSQL & " tb1.○△□ LIKE ('%'+ tb2.○△□ + '%') "
strSQL = strSQL & " ;"

rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

strSQL = ""
strSQL = strSQL & " SELECT *"
strSQL = strSQL & " FROM T_○△□品番"
strSQL = strSQL & " ;"

rsS.Open strSQL, cn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then

    Do Until rs.EOF
        rsS.AddNew

        rsS!品番 = Nz(rs!品番, "")
        rsS!○△□ = Nz(rs!○△□, "")

        rsS.Update
        rs.MoveNext

    Loop

End If

rs.Close: rsS.Close

cn.Close: Set cn = Nothing
Set rs = Nothing
Set rsS = Nothing

End Sub

おススメのSQL本はこちら

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