全てにおいてDlookUpで重要なこと
- すべての項目を二重引用符で囲むことを忘れてはいけない。
- 検索条件式(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)に◎◎市〇〇区と入っているので、市で切って、市名を抜き出している。
公式の解説がおかしい
公式の解説はクエリに式を入れる場合と異なっており、エラーが起きるのももっともである。だが、それを説明していない。