条件にあったデータの取得 DLookup関数の使い方

VBA

Accessでデータ操作を行う際に、ある一覧から条件にあったレコードの値を取得したいということがあります。Excelでは、VLookup関数やHLookup関数として様々なシーンで使用されていると思いますが、Accessではこれらと似た関数として「DLookup関数」が用意されています。

スポンサーリンク

DLookup関数の構文

DLookup( expr, domain [, criteria] )

DLookup関数の構文には、次の引数があります。

引数説明
expr必須項目で、値を返すフィールドを指定する。
domain必須項目で、レコードセットを識別する。テーブル名やクエリ名を記載する。
criteria省略可能。ここで指定された条件に基づき、exprの値を返す。省略した場合は、domain内のランダム値を返す。条件に合うデータがない場合はNullを返す。

これだけでは、実際に使う場合のイメージがわきにくいので、次からテストデータを用いてDLookup関数の動きを見てみましょう。

テストデータについて

DLookup関数は、あるフィールドに対して設定した条件に一致するレコードについて、同じく指定したフィールドの値をを出力するため、対象となるテーブルは、マスタテーブルになることが多いです。そこで、次のようなテストデータを作成しました。なお、作成するテーブル名称は「T_品目マスタ」とします。

■作成するサンプルテーブルの構造

フィールド名データ型
品目IDオートナンバー型
品名短いテキスト
型番短いテキスト
製造元短いテキスト

サンプルテーブルには、次のデータが含まれているものとします。

■作成するサンプルレコード

品目ID品名型番製造元
1えんぴつ(HB)EN-001四菱
2消しゴムKE-001四菱
3えんぴつ(B)EN-002四菱
4定規JY-001TOMBO
5ノートNO-001コクミ

次からVBAのコーディング説明になります。VBAの使用方法については以下の書籍をご参考にしてください。著者も、これらの書籍を辞書代わりに愛用しています。

スポンサーリンク

DLookup関数からのデータ抽出

それでは、テストデータからDLookup関数を用いて必要なデータを抽出してみましょう。まずは、「作成」タブの「マクロとコード」欄から「標準モジュール」を選択します。続いて、起動したVBA画面の「挿入」タブで「プロシージャ」を選択し、名前を「Dlookup_Sample」、Subプロシージャ、Publicプロシージャと設定します。詳しくは、以下の記事をご参照ください。

そして次のようにコードを記載してください。

Public Sub Dlookup_Sample()
  
  'この1行を追加する。
  MsgBox Dlookup("品名", "T_品目マスタ", "型番 = 'EN-001'")

End Sub

さて、上で紹介したコード中のDlookup関数では、「T_品目マスタ」テーブルの内、「型番」フィールドの値が「EN-001」のレコードにおける、「品名」フィールドの値を出力するという意味です。したがって、型番が「EN-001」である「えんぴつ(HB)」がメッセージボックスで表示されることになります。

実際にVBA画面上のSub/ユーザーフォームの実行アイコンである、緑三角ボタン「▶」をクリックしてください。すると、画面上に「えんぴつ(HB)」というメッセージボックスが現れたと思います。

なお、抽出条件に文字列を使う場合、シングルクォーテーション「’」で、日付型の場合は「#」で括ります。また、数値型の場合はそのまま記入します。

条件に変数を適用する方法

先に紹介した方法では、criteriaに型番の値を直接入力しましたが、変数にすることで任意の値を出力できるようになります。

例えば、InputBox関数、Nz関数(値がNullであれば、条件に応じた別の値を返す関数)とIf文を組み合わせることで対話型の品名確認ができるようになります。

Public Sub Dlookup_Sample()

  '型番を格納する変数を宣言
  Dim param As String

  'InputBox関数でユーザーに値を入力してもらい、その値をparam変数に格納する。
  param = InputBox("型番入力")
  
  'DLookup関数で品名を呼び出す際に、param変数を抽出条件に用いている。
  'DLookup関数で対象がない場合にはNullが返されるが、
  'Nz関数を用いてNullの代わりに「empty」を返すようにしている。
  MB = Nz(Dlookup("品名", "T_品目マスタ", "型番 = '" & param & "'"), "empty")

  'If文で空の場合と、品名を返す場合を分岐させる。
  If MB = "empty" Then
    MsgBox "対象の品はありませんでした。"
  Else
    MsgBox MB
  End If
 
End Sub

入力した型番が、「T_品目リスト」にある場合は、対象となる品名が、そうでない場合は、対象がないとのアラートがでるようになったと思います。

まとめ

Accessでデータ操作を行う際のテクニックとして、DLookup関数を紹介しました。特に、マスタテーブルを操作するときに、DLookup関数を活用する機会は多くあると思いますので、本記事を参考にしていただければと思います。

特に、フォームを作成してユーザーとの対話型によるデータ操作をする場合には有効活用できるのではないでしょうか。

上手く機能を利用して、業務効率を改善していただければと思います。

以下で紹介している書籍は、Accessで行いたい処理を逆引きする際に活用できますので、是非お手元に用意して、今後のAccess開発に臨んでください!

スポンサーリンク

コメント

タイトルとURLをコピーしました