Accessでの禁止文字チェック

Question

http://oshiete.goo.ne.jp/qa/5536177.html

こちら↑でExcel版の回答をみつけましたが、
同じことをAccessでやる場合の関数や呼び出し方がわかりません。

※上記質問を一部改変して掲載させていただきます。
　禁止文字ではなく許可文字の一覧を用意している点が異なります。

【Accessでやりたいこと】
「◆データ一覧」に、「◆許可文字一覧」が以外の文字が含まれているかをデータチェックしたい。
以下の場合だと、「◆データ一覧の3レコードめ」をエラーレコードとして表示したい。

------------------------------------------
◆データ一覧テーブル
　　　　　　　　　名称
1レコードめ　　アイウ
2レコードめ　　Ａ３
3レコードめ　　ア・Ａ

◆許可文字一覧テーブル
　　　　　　　　　文字
1レコードめ　　ア
2レコードめ　　イ
3レコードめ　　ウ
4レコードめ　　Ａ
5レコードめ　　１
6レコードめ　　２
7レコードめ　　３
------------------------------------------

以上、よろしくお願い致します。

30246kiku · Accepted Answer

＃２です。

> Dim rs As New ADODB.Recordset　行で
> で「ユーザ定義型は定義されていません」が出てしまい、

VBE のメニュー「ツール」→「参照設定」で、
Microsoft ActiveX Data Objects X.Y Library　を追加してみてください。

X.Y は、2.5　とか　2.8　とか。
2007 をメインで使ってますが、2000 形式にしたりするので、私は 2.5 を参照してますけど。

なお、DAO でする場合は以下のような感じに

Public Function DicMake() As Boolean
　　Dim rs As DAO.Recordset

If (dic Is Nothing) Then Set dic = CreateObject("Scripting.Dictionary")
　　dic.RemoveAll

Set rs = CurrentDb.OpenRecordset(sTable)
　　While (Not rs.EOF)
　　　　dic.Item(rs(sField).Value) = Null
　　　　rs.MoveNext
　　Wend
　　rs.Close
　　Set rs = Nothing
　　DicMake = True
End Function

仕様がもう少し固まっていれば・・・記述も変わってきます。

＃２では、
「名称」内に許可文字が複数回出現しても、それは許可しています。
（「名称」が "アイアイ" であっても許可）

また、「文字」が１文字長でなくても処理できるように冗長にしています。が、

許可の「文字」が、"アイ" "ア" であった場合、処理順を考える必要があります。
"アイアイ" を "アイ" "ア" の順で処理すると、全て削除され表示対象になりませんが、
"ア" "アイ" の順で処理すると、"イイ" が残ります。

許可の「文字」が、"イイ" "ア" であった場合、処理順を考える必要があります。
"アイアイ" を "ア" "イイ" の順で処理すると、全て削除され表示対象になりませんが、
"イイ" "ア" の順で処理すると、"イイ" が残ります。

レコードセットを得る時等で、順を考える必要があります。
テーブル名を指定する際に、SQL記述にして順を指定・・・とか
例えば、クエリで見た時の記述（文字列長が長い順で、昇順）

SELECT * FROM 許可文字一覧
ORDER BY Len(文字) DESC, 文字;

を指定するとか・・・
また、出現頻度が高い順に得られていると、少ないループで処理できると思います。

「文字」は１文字長しかない場合、以下の様な記述でも良いのかも・・・
（クエリに変更はありません）

Public Function DicCheck(vSrc As Variant) As Boolean
　　Dim sS As String
　　Dim i As Long

DicCheck = True
　　If (dic Is Nothing) Then Exit Function
　　If (dic.Count = 0) Then Exit Function
　　sS = vSrc & ""
　　For i = 1 To Len(sS)
　　　　If (Not dic.Exists(Mid(sS, i, 1))) Then Exit Function
　　Next
　　DicCheck = False
End Function

> キャッシュに展開せずに、DicCheck　だけを実行する形に
> 変えようとしていますが、うまく修正できません。

＃２での 削除する（Replace を使う）方法で毎回レコードセットを得るものにすると
（DAO での例になります）

Public Function DicCheck(vSrc As Variant) As Boolean
　　Dim rs As DAO.Recordset
　　Dim sS As String

DicCheck = True
　　sS = vSrc & ""
　　Set rs = CurrentDb.OpenRecordset("許可文字一覧")
　　Do While (Not rs.EOF)
　　　　sS = Replace(sS, rs("文字"), "")
　　　　If (Len(sS) = 0) Then Exit Do
　　　　rs.MoveNext
　　Loop
　　rs.Close
　　Set rs = Nothing
'　　sS = Trim(sS)
　　If (Len(sS) = 0) Then DicCheck = False
End Function

の記述だけで、クエリは

SELECT * FROM データ一覧 WHERE DicCheck(名称);

でいけると思います。

「文字」が１文字長しかなかった場合は、FindFirst で検索し、
NoMatch で判断する等いろいろ考えられると思います。

> 許可文字数が150字程度と少ないので
> キャッシュに展開しない方法をご教授いただけないでしょうか。

おそらく、
処理スピードは許可文字の件数ではなく、「名称」の件数に大きく左右されると思います。
（「名称」の件数がどれだけかわかりませんが、１件ごとに OpenRecordset するので・・・）
環境にあった速い方法を見つけられたらと思います。
（Dictionary 展開が速い・・・・　わかりません）

hatena1989 · Answer

> 許可文字数が150字程度と少ないので
> キャッシュに展開しない方法をご教授いただけないでしょうか。

ということなら、許可文字一覧を1文字１レコードではなく、１レコードに
すべて入れてしまえばどうでしょうか。

◆許可文字一覧テーブル
　　　　　　　　　文字
1レコードめ　　アイウＡ１２３

標準モジュールに下記の関数。

Public Function MeisyouError(Meisyou, ArrowChars As String) As Boolean
　　Dim c As String
　　Dim i As Long
　　MeisyouError = True
　　If IsNull(Meisyou) Then Exit Function
　　For i = 1 To Len(Meisyou)
　　　　c = Mid(Meisyou, i, 1)
　　　　If InStr(ArrowChars, c) = 0 Then
　　　　　　MeisyouError = False
　　　　　　Exit For
　　　　End If
　　Next
End Function

クエリ

SELECT データ一覧.*
FROM データ一覧, 許可文字列
WHERE MeisyouError([名称],[文字]);

hatena1989 · Answer

30246kikuさんのコード見さていただきました。さすがですね。
Dictionary を使う所など参考になります。

私のクエリの直積を使う方法は、許可文字数やレコード数が多くなると、かなり重くなりそうです。

hatena1989 · Answer

> データ一覧].名称　は同じ文字を複数含むケースがあるため
> 許可文字だけの場合もエラーレコードとなってしまいます。

そのケースを考慮するのを失念してました。

Q_許可文字数 を下記のように変更してください。

SELECT
 データ一覧.名称,
 Sum(Len([名称])-Len(Replace([名称],[文字],""))) AS 許可文字数
FROM データ一覧, 許可文字一覧
GROUP BY データ一覧.名称;

これで文字に重複があっても大丈夫だと思います。

30246kiku · Answer

一例で良いですか。

VBA でユーザ定義関数を作って、それを呼び出して処理します。

「名称」の文字列から「文字」を１つ１つ削除していき、
最終的に何らかの文字が残っていたら・・・・と考えてみます。
１つ１つを削除する時には、Replace を使用します。
ここで、毎回レコードセット（「文字」を得て、クルクル回す時）を得ていると
遅くなると思うので、「文字」を Dictionary に展開しておいて、メモリ上で処理・・・

「文字」を Dictionary に展開　：　DicMake
何らかの文字が残るか　　：　DicCheck
（関数名は適宜変更してください）

標準モジュールに以下を記述します。

Private Const sTable As String = "許可文字一覧"
Private Const sField As String = "文字"
Private dic As Object

Public Function DicMake() As Boolean
　　Dim rs As New ADODB.Recordset

If (dic Is Nothing) Then Set dic = CreateObject("Scripting.Dictionary")
　　dic.RemoveAll

rs.Open sTable, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While (Not rs.EOF)
　　　　dic.Item(rs(sField).Value) = Null
　　　　rs.MoveNext
　　Wend
　　rs.Close
　　DicMake = True
End Function

Public Function DicCheck(vSrc As Variant) As Boolean
　　Dim sS As String
　　Dim v As Variant

DicCheck = True
　　If (dic Is Nothing) Then Exit Function
　　If (dic.Count = 0) Then Exit Function
　　sS = vSrc & ""
　　For Each v In dic.keys
　　　　sS = Replace(sS, v, "")
　　　　If (Len(sS) = 0) Then Exit For
　　Next
'　　sS = Trim(sS)
　　If (Len(sS) = 0) Then DicCheck = False
End Function

※ ADO を使ってますが、DAO でも・・・・
※ Trim は必要に応じて・・・・

このユーザ定義関数を使って、クエリを作ります。
（SQLビュー表示にして）

SELECT * FROM データ一覧
WHERE DicMake() AND DicCheck(名称);

※ パラメータ（引数）を持たない DicMake() は１度しか呼ばれません。
なので、WHERE の１番最初に呼び出すことで 「文字」がメモリ上に展開されます。

参考になるかどうか、しっかりと検証はしてください。

hatena1989 · Answer

下記のようにクエリを2段に使うことで可能です。SQLが分からない場合は、クエリのデザインビューの画像を参照してください。

名称に含まれる許可文字数を取得するクエリ

Q_許可文字数
SELECT [データ一覧].名称, Count(許可文字一覧.文字) AS 許可文字数
FROM データ一覧, 許可文字一覧
WHERE [データ一覧].名称 Like "*" & [文字] & "*"
GROUP BY [データ一覧].名称;

このクエリとデータ一覧テーブルからクエリを作成
実際の文字数より許可文字数が少ないレコードを抽出

Q_名称エラー
SELECT [データ一覧].名称
FROM データ一覧 LEFT JOIN Q_許可文字数 ON [データ一覧].名称 = Q_許可文字数.名称
WHERE Nz([許可文字数],0)<Len([データ一覧].[名称]);

SQLが理解できるなら、サブクエリを使えば一つのクエリにまとめることができます。

Accessでの禁止文字チェック

＃２です。

> 許可文字数が150字程度と少ないので

30246kikuさんのコード見さていただきました。

> データ一覧].名称 は同じ文字を複数含むケースがあるため

一例で良いですか。

この回答への補足

下記のようにクエリを2段に使うことで可能です。

この回答への補足

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

> データ一覧].名称　は同じ文字を複数含むケースがあるため