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

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

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

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

#MSの説明(改変)
[DLookup 関数](https://support.office.com/ja-jp/article/DLookup-関数-8896cb03-e31f-45d1-86db-bed10dca5937)
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 |北海道|

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

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

#具体例3 VBAで使っている例
[[VBA]日本の都道府県名、地方公共団体名を抜き出す 3 総務省の地方公共団体コードからテーブルを作る](https://qiita.com/Q11Q/items/710e79f2cad153a69829)

```vbnet
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)に◎◎市〇〇区と入っているので、市で切って、市名を抜き出している。

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