Excelの改行セルをAccessのクエリで一致検知する方法

ExcelからAccessにデータをインポートする際、1セル内の改行を保持したまま取り込むことができますが、そのままではクエリで一致を検知するのが難しくなります。この記事では、Excelの元データを変更せずに、Access側でデータを分割し、一致検知を行う方法を解説します。

Accessで改行を含むセルのデータをクエリで検索する方法
まとめ

Accessで改行を含むセルのデータをクエリで検索する方法

1. Accessの改行コードを理解する

Excelではセル内の改行は「CHAR(10)（LF: Line Feed）」として扱われますが、Accessでは「Chr(13) & Chr(10)（CRLF: Carriage Return + Line Feed）」が使用される場合があります。そのため、検索の際には改行コードの違いを考慮する必要があります。

2. Accessのクエリで改行を考慮した検索を行う

Excelからインポートされたデータが1セル内で改行されている場合、そのままでは一致を検知できません。Accessのクエリで、改行を考慮した検索を行う方法を説明します。

方法①：LIKE演算子を利用する

Accessのクエリで「LIKE」演算子を使用すると、部分一致検索が可能です。例えば、特定の値「1111111111」を含むデータを検索するには、次のようなクエリを使用できます。

SELECT * FROM テーブル名 WHERE フィールド名 LIKE "*1111111111*";

このクエリは、「1111111111」がセル内のどこにあっても一致を検知できます。

方法②：Replace関数を使って改行をスペースに置換

改行を削除またはスペースに変換して、比較をしやすくする方法もあります。次のようなクエリを使用すると、改行をスペースに変換できます。

SELECT *, Replace([フィールド名], Chr(13) & Chr(10), " ") AS 変換後フィールド FROM テーブル名;

このクエリを実行すると、改行がスペースに置き換わるため、検索が容易になります。

3. VBAを使用してデータを分割する

AccessのVBAを使って、改行で区切られたデータを個別のレコードに分割し、別のテーブルに保存する方法もあります。以下のVBAコードを実行すると、改行を基準にデータを分割できます。

Function SplitData()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsNew As DAO.Recordset
    Dim strData As String
    Dim arrData As Variant
    Dim i As Integer
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT ID, フィールド名 FROM テーブル名;")
    Set rsNew = db.OpenRecordset("新テーブル名", dbOpenDynaset)
    
    Do While Not rs.EOF
        strData = rs!フィールド名
        arrData = Split(strData, Chr(13) & Chr(10))
        
        For i = LBound(arrData) To UBound(arrData)
            rsNew.AddNew
            rsNew!ID = rs!ID
            rsNew!分割フィールド = arrData(i)
            rsNew.Update
        Next i
        
        rs.MoveNext
    Loop
    
    rs.Close
    rsNew.Close
    Set rs = Nothing
    Set rsNew = Nothing
    Set db = Nothing
End Function

このスクリプトを実行すると、元データを改行で分割し、別のテーブルに保存できます。