accessの別テーブルの情報を利用して、文字列の一部を置換又は削除する

前提・実現したいこと

たとえば
Aテーブル内の一定の文字列の中に、
不用な"スペース"や
文字列内の特定の文字を置換したり、
文字列内の特定の文字を削除したりしたい。
特定の文字を別のBテーブルで、変換前、変換後として、設定しておいて、
(↑部分一致の場合)

また、同じように
Cテーブルにも、変換前、変換後として、設定しておいて、
こちらは、テーブルセル内の文字について、全文字一致するものだけ置換する
(↑完全一致の場合)

以下は、以上の内容をエクセルでやったみたものですが、accessで同様の処理を行いたい。
Sub 作業03完全一致()
ActiveSheet.TransitionFormEntry = True
i = 2
Do
x1 = Sheets("完全一致変換リスト").Cells(i, 1)
x2 = Sheets("完全一致変換リスト").Cells(i, 2)
Sheets("評価額データ取込み").Columns("E:E").Replace _
What:=x1, Replacement:=x2, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True
i = i + 1
Loop Until Sheets("完全一致変換リスト").Cells(i, 1) = ""
End Sub

Sub 作業04一部一致()
ActiveSheet.TransitionFormEntry = True
i = 2
Do
x1 = Sheets("部分一致変換リスト").Cells(i, 1)
x2 = Sheets("部分一致変換リスト").Cells(i, 2)
Sheets("評価額データ取込み").Columns("E:E").Replace _
What:=x1, Replacement:=x2, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True
i = i + 1
Loop Until Sheets("部分一致変換リスト").Cells(i, 1) = ""
End Sub

試したこと

UPDATE TData, TReplace
SET TData.FData = Replace([TData].[FData],[TReplace].[Fld1],[TReplace].[Fld2])
WHERE TData.FData Like "" & TReplace.Fld1 & "";

これはやってみましたが、なぜか全部空欄になってしまうセルがでました。

補足情報（FW/ツールのバージョンなど）

access2019です
ここにより詳細な情報を記載してください。

行動規範の内容に同意します

回答1件

ベストアンサー

部分一致の場合は提示のSQLでいいと思ったのですが、サンプルを作成してやってみるとダメですね。
以前同じようなことをやったことがあったのですが、そのときはできたはずなんですが。（仕様変更があったのかな？）

下記のようにReplace関数の引数を省略せずに記述したらうまくいきました。

sql
1UPDATE TData, TReplace
2SET TData.FData = Replace(TData.FData,TReplace.Fld1,TReplace.Fld2,1,-1,0)
3WHERE TData.FData Like "*" & TReplace.Fld1 & "*";

完全一致の置換は下記のSQLでうまくいくと思います。

sql
1UPDATE TData INNER JOIN TReplace2 ON TData.FData = TReplace2.Fld1
2SET TData.FData = TReplace2.Fld2;

投稿2021/03/28 11:40

hatena19

総合スコア34053

YEBISU2021

2021/03/29 13:03

ご回答ありがとうございます。文字→文字の変換は出来るようですが、文字→""(削除)がうまくいかないようです。削除に関してだけ、別作業とした方がいいのでしょうか? 変換後のセルにも""の入力は出来ないようなので、現在は空欄にしていました。

hatena19

2021/03/30 00:40

空欄フィールドの値がNullになっているが原因だと思われます。下記のように設定すると、空欄フィールドは ""（空文字列）になりますのでうまく動作するようになると思います。 TReplace をデザインビューで開いて、Fld1, Fld2 のフィールドプロパティの「値要求」を「はい」、「空文字列の許可」を「はい」に設定してください。

YEBISU2021

2021/03/30 14:07

ありがとうござます。やってみました! 今度はサンプルではなくて、本データでやってみました。フィールドは13なんですが、置換対象はそのうちのフィールド5番目で、、、、ところが、データ数が14万行あって、しばらく動いてましたが、2G超えて、エラー表示になりました・・・・あれ?エクセルで出来たので、処理容量を超えることは想定してなかったのですが・・・

hatena19

2021/03/30 14:18

テーブルを TData, TReplace というように２つ並べて記述すると、直積結合(交差結合)というものになり、２つのテーブルの件数をかけた件数のデータが生成されますので、テーブルの件数が多いと重くなったりエラーになったりします。 14万レコードを一気にやらずに、例えば1万レコードずつ抽出して、それを対象に更新するというようにしてみてください。

YEBISU2021

2021/03/30 22:36

なるほど、色々と丁寧なご説明ありがとうございます。要求値のあたりなんかは、教えてもらわないと、私一人ではたどり着けない部分だったと思います。また、これだけの大量データを使用するのも今回初のことだったので、とても参考になりました。もう少し、頑張ってみます！m(_ _)m

行動規範の内容に同意します