VBA
access
DLOOKUP

ACCESS VBA DLOOKUP関数覚書 マイクロソフト公式が教えてくれない罠

全てにおいてDlookUpで重要なこと

  1. すべての項目を二重引用符で囲むことを忘れてはいけない。
  2. 検索条件式(Creteria)は値をシングルクォーテーションで囲まなければならない。

これは基本。今日忘れていた...

MSの説明(改変)

DLookup 関数
Access / 式 / ドメインの関数 / DLookup 関数
適用対象: Access 2016, Access 2013, Access 2010, Access 2007
DLookup( expr, domain [, criteria] )

テーブル/クエリ用に改変

DLookUp(返り値を返す計算式, ソースになるテーブルまたはクエリ名 [,検索条件式])

DLookup 関数を使って、フォームやレポートのレコードソース内に存在しない(別のテーブルやクエリ、フォーム、レポートの)フィールドの値を表示できる。
たとえば、"注文の詳細" テーブルに基づくフォームがあるとする。
このフォームには、
"注文 ID"、"製品 ID"、"単価"、"数量"、"ディスカウント" ,"製品名"
 (ソースのテーブルは"注文の詳細" テーブル)
の各フィールドが表示される。
ただし "製品名" フィールドは、"製品" テーブルという別のテーブルにある。
演算コントロールで DLookup 関数を使って、同じフォームに製品名を表示することができる。
DLookup 関数を使って、クエリ内の [条件] 行、クエリ式の集計フィールド、または 更新クエリ の [更新対象] 行で条件を指定できる。
また、表示する必要があるフィールドが、フォームやレポートのベースとなるレコード ソース内に存在しない場合、フォームやレポートの演算コントロールの式で DLookup 関数を使うこともできる。
たとえば、"注文詳細" テーブルに基づく "注文詳細" フォームがあり、そのフォームに "製品 ID" フィールドを表示する [製品 ID] という文字箱(テキスト ボックス)があるとする。 文字箱の値に基づいて "製品" テーブルから製品名を検索するには、別の文字箱を作成し、その操作源(ControlSource) プロパティを次の式のように設定する。

=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

MSの公式のこの解説の式は文字箱の操作源に設定した事例。クエリにテーブルをソースとして式をフィールドに入れる式はエラーになる。というのが今回のポイント。

引数 説明
expr 必須。 値を返すフィールドを識別する式、 テーブルやクエリ内のフィールドを識別する文字列式であることもあれば、対象フィールド内のデータに対して計算を実行する式である場合もあります。 expr では、テーブル内のフィールド、フォームのコントロール、定数、または関数の名前を指定することができます。 expr で関数を指定する場合、その関数は組み込みでもユーザー定義でもかまいませんが、別の定義域(本記事の場合ソースのテーブルではない別のテーブルのこと)の集計関数または SQL 集計関数は使えません。
定義域(本記事の場合ソースのテーブルのこと) 必須。 定義域(本記事の場合ソースのテーブルのこと)を構成するレコード セットを識別する文字列式。 テーブル名の場合もあれば、パラメーターを必要としないクエリのクエリ名の場合もあります。
criteria(検索条件式) 省略可能。 DLookup 関数の実行対象となるデータ範囲を制限する文字列式(本記事では検索条件式と書いている)。 たとえば、検索条件式 は多くの場合、WHERE という語句がないだけで、SQL 式の WHERE 句と同等です。 Creteria(検索条件式) を省略した場合、DLookup 関数は、定義域(本記事の場合ソースのテーブルのこと)全体に対して expr を評価します。 Creteria(検索条件式) に含めるすべてのフィールド(列)は、定義域(ソースのテーブル) 内のフィールド(列)であることも必要です。そうでない場合、DLookup 関数は Null を返します。

マクロ、モジュール、クエリ式、または演算コントロールのいずれで DLookup 関数を使っても、正しく評価されるように creteria検索条件式を慎重に構築する必要があります。

ヒント

DLookup 関数を使って外部テーブルのフィールドから抽出した値を表示することはできるが、両方のテーブルから値を抽出する必要があるフィールドを含むクエリを作成してから、そのクエリに基づいてフォームやレポートを作成する方が効率的な場合がある。
また、ルックアップ ウィザードを使って、外部テーブルの値を検索することもできる。

具体例

目的

T_市区町村Codeテーブルに市町村のコードがあるので、このテーブルの「市町村名」フィールドと合致する「団体コード」フィールドのコードを返り値として
「T_所在地」
の市町村コードを更新する。(更新クエリ)

方法

「F03所在地」フィールドの Like "黄色村"
で抜き出し
T_市区町村Code

「市町村名」フィールドから黄色村に該当するレコードの団体コードを返す
DLookUp("団体コード","T_市区町村Code","市町村名 = '黄色村'")

この式のポイント

 DLOOKUPをクエリ、テーブルに用いる場合フォームと表記が異なることがある。またフォームと同じにするとエラーになる。

expr

"[T_市区町村Code]![団体コード]"
"[T_市区町村Code].[団体コード]"
という表示はエラーになる
"[団体コード]"
これもエラー。MSの公式の説明はフォーム/レポートのコントロールソースのみのようだ。
クエリに用いる場合、以下のように、フィールド名のみにして二重引用符で囲む。
"団体コード"

定義域(本記事の場合ソースのテーブルのこと) Domain

この場合はもとになるテーブル名のことを言う。(クエリも使える)
しかし exprと同様に
"[T_市区町村Code]"
はだめで、テーブル名のみにする。

criteria 検索条件式

書き方
"フィールド名 = '文字列'"

ポイント

検索条件式自体を半角の二重引用符で囲む
検索条件になる文字列は半角のアポストロフ(シングルクォーテーション)で囲む
ここでもフィールド名を[]で囲まないまたテーブル名をつけない

悪い例
"[テーブル]![フィールド] = '文字列'" [テーブル名] は付けてはならない
"[テーブル].[フィールド] = '文字列'"
"[フィールド] = '文字列'" []は付けない
"テーブルフィールド = 文字列" 文字列をアポストロフで囲んでいない

SQLに表示させるとこんな感じになる

UPDATE T_所在地 SET T_所在地.F20市町村コード = DLookUp("団体コード","T_市区町村Code","市町村名= '黄色村'") WHERE (((T_所在地.[F03所在地]) Like "黄色村"));
さらにこれをVBAに書くとこうなる
cityname = "黄色村"
ssql = "UPDATE T_所在地 SET T_所在地.F20市町村コード = DLookUp(""団体コード"",""T_市区町村Code"",""市町村名= '" & cityname & "'"") WHERE (((T_所在地.[F03所在地]) Like """ & cityname & """));"

  • criteria に含めるすべてのフィールドは、domain 内のフィールドであることも必要とは 検索条件式は一つのテーブルの中のフィールドだけ使える。違うテーブルのフィールドは使えない。ということを言っている。
  • Exprは計算式でもよいが、ソーステーブルの中のフィールド以外は使えない。
  • クエリもソースとして使えるが、パラメータークエリは使えない。

具体例2

目的

フォームで
T_都道府県コード
というテーブルを用い
1
をiに代入して
北海道
という都道府県名を求める

テーブル名 T_都道府県コード

F00都道府県コード F01都道府県名
1 北海道

i = 1
strPre = DLookup("F01都道府県名", "T_都道府県コード", "F00都道府県コード" = i)

ポイント

数値の場合すべてをダブルクォーテーションで囲むのではなく、フィールド名だけを囲む。
シングルクォーテーションも不要で、文字列の場合と異なる。

具体例3 VBAで使っている例

[VBA]日本の都道府県名、地方公共団体名を抜き出す 3 総務省の地方公共団体コードからテーブルを作る

rs.Fields("F04都市名ヨミ") = DLookup("[F05市区町村名ヨミ]", "T_JPLocalGovCode", "[F03市区町村名] = " & "'" & Mid(adRS(1), 1, InStr(1, adRS(1), "市", vbTextCompare)) & "'" & "")

ポイント

T_JpLocalGovCodeテーブルのF03市町村名と一致するF05市区町村名ヨミをF04都市名ヨミフィールドに代入している。
この時、F05市区町村名は"[]"で囲んでいる。また "[F03市区町村名] = "に続けて、 & "'" &の部分がシングルクォーテーションで囲み始めている部分。Mid以下はadRs(1)に◎◎市〇〇区と入っているので、市で切って、市名を抜き出している。

公式の解説がおかしい

 公式の解説はクエリに式を入れる場合と異なっており、エラーが起きるのももっともである。だが、それを説明していない。