Accessでの禁止文字チェック

質問

http://okwave.jp/qa/q5536177.html

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

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

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

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

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

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

回答No.3

30246kiku · ベストアンサー

＃２です。

> 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 展開が速い・・・・　わかりません）

お礼

お礼が遅くなり申し訳ありません。
ライブラリを追加してキャッシュに展開するロジックを
活用させていただきました。

何度も丁寧な回答をありがとうございました。

回答No.6

hatena1989

> 許可文字数が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([名称],[文字]);

お礼

ありがとうございました。

回答No.5

hatena1989

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

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

お礼

ありがとうございました。

回答No.4

hatena1989

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

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

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

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

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

お礼

お礼が遅くなり申し訳ありません。

質問させていただいた機能は＃２さんのロジックを使いましたが、
別の機能で文字数チェックをする必要があり
さっそく回答いただいたSQLを活用させていただきました。

どうもありがとうございました。

回答No.2

30246kiku

一例で良いですか。

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 の１番最初に呼び出すことで 「文字」がメモリ上に展開されます。

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

お礼

回答ありがとうございました。
補足で再度質問させていただいていますが
こちらも回答いただけるととても有難いです。
引き続きどうぞよろしくお願い致します。

補足

早速回答ありがとうございました。

関数を定義して実行してみたところ、
Dim rs As New ADODB.Recordset　行で
で「ユーザ定義型は定義されていません」が出てしまい、
試しにDim rs As New DAODB.Recordset　と書き変えたりしたのですが
如何せん知識不足で対処できませんでした。

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

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

どうぞよろしくお願い致します。

回答No.1

hatena1989

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

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

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

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

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

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

お礼

回答ありがとうございました。
補足で再度質問させていただいていますが
こちらも回答いただけるととても有難いです。
引き続きどうぞよろしくお願い致します。

補足

早速ありがとうございました。

SQLは理解できたのでクエリを作成してみました。
うまく動いたのですが、一つ問題がみつかりました。

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

（例） [データ一覧].名称　アイウアイ
　　　　許可文字数　　　　　３
　　　　Len　　　　　　　　５

自分でも試行錯誤しているところですが
なにかアイデアお持ちでしたらレクチャお願いいたします。