Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2010)
Dlookup 複数条件(コード・日付)
投稿日時: 18/07/13 15:04:10
投稿者: にーさん

お世話になります、先に進まなくて困っています。
 
受注エントリーで出荷日と商品CDを入力すると、商品単価マスタが該当の単価を表示させたいのですが、うまく出来ずに困っています。
 
○T9_商品単価マスタ
 商品CD
 開始日付
 単価
があるとします。
 
マスタ入力で下記の項目を入力しています
 商品CD 開始日付  単価
 0001  2015/04/01 1000
 0001  2018/06/01 1200
 0002  2015/10/01  800
 0003  2017/04/01  500
 
で、受注エントリーで
 出荷日(2018/04/10) 商品CD(0001) と入力すると単価(1000)
 出荷日(2018/07/02) 商品CD(0001) と入力すると単価(1200)
と表示して欲しいのですが、思っているようになりません
 
【商品CD】更新後処理 出 [イベントプロシージャ]処理で
 
Private Sub 商品CD_AfterUpdate()
  
    Dim WK単価 As Long
     
    Me.商品名1.Value = DLookup("商品名1", "T9_商品マスタ", "商品CD = " & Me.商品CD)
    Me.商品名2.Value = DLookup("商品名2", "T9_商品マスタ", "商品CD = " & Me.商品CD)
 
    WK単価 = DLookup("単価", "T9_商品単価マスタ", "商品CD=" & Me.商品CD & " And 開始日付 < #" & Forms![F1_受注メインフォーム]![出荷日] & "#")
 
End Sub
 
出荷日(2018/04/10) 商品CD(0001) と入力して単価 (1000)
出荷日(2018/07/02) 商品CD(0001) と入力しても単価(1000)となってしまいます。
 
よろしくお願いします。
 

回答
投稿日時: 18/07/13 15:53:08
投稿者: sk

引用:
DLookup("単価", "T9_商品単価マスタ", "商品CD=" & Me.商品CD & " And 開始日付 < #" & Forms![F1_受注メインフォーム]![出荷日] & "#")

引用:
出荷日(2018/04/10) 商品CD(0001) と入力して単価 (1000)
出荷日(2018/07/02) 商品CD(0001) と入力しても単価(1000)となってしまいます。

指定された抽出条件に該当するレコードが 2 件以上存在する場合、
そのうちのどのレコードのフィールドの値を返すか
誰にも判らないというのが DLookup 関数の特徴です。
 
仮に Recordset オブジェクトを使用せずに、
Access の定義域集計関数のみで実現なさりたいならば、
 
1. [T9_商品単価マスタ]のレコードのうち、
   フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
   かつフィールド[開始日付]の値がテキストボックス[出荷日]の値より小さい
   レコードの、[開始日付]の最大値を取得する。
 
2. 更に[T9_商品単価マスタ]のレコードのうち、
   フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
   かつフィールド[開始日付]の値が上記 1 で得た[開始日付]の最大値に等しい
   レコードの[単価]の値を取得する。
    
という流れでないと、正確な[単価]の値を取得できないでしょう。

投稿日時: 18/07/13 16:32:22
投稿者: にーさん

skさん、早速の見解ありがとうございます。
 
 

引用:

1. [T9_商品単価マスタ]のレコードのうち、
   フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
   かつフィールド[開始日付]の値がテキストボックス[出荷日]の値より小さい
   レコードの、[開始日付]の最大値を取得する。
 
2. 更に[T9_商品単価マスタ]のレコードのうち、
   フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
   かつフィールド[開始日付]の値が上記 1 で得た[開始日付]の最大値に等しい
   レコードの[単価]の値を取得する。
    
という流れでないと、正確な[単価]の値を取得できないでしょう。

 
確かにその通りだと思います。
 
Dlookupも条件がひとつなら、今まで作ったことがあるのですが、複数条件は作成したことがなく
ネットで調べて、やっとここまでたどり着いたので、言われることは分かるのですが、それを構文
にする力がありません。
 
どうやってするのか教えて頂くことはできませんか?

回答
投稿日時: 18/07/13 17:26:12
投稿者: sk

引用:
言われることは分かるのですが、それを構文にする力がありません。

引用:
WK単価 = DLookup("単価", "T9_商品単価マスタ", "商品CD=" & Me.商品CD & " And 開始日付 < #" & Forms![F1_受注メインフォーム]![出荷日] & "#")

Dim MAX開始日付 As Variant
 
'[T9_商品単価マスタ]のレコードのうち、
'フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
'かつフィールド[開始日付]の値がフォーム[F1_受注メインフォーム]の
テキストボックス[出荷日]の値より小さいレコードの
'[開始日付]の最大値を取得する。
MAX開始日付 = DMax("開始日付", "T9_商品単価マスタ", _
                   "[商品CD]=" & Me.商品CD & _
                   " And [開始日付] < #" & Forms![F1_受注メインフォーム]![出荷日] & "#")
 
'MAX開始日付 の値が Null である(該当レコードが1件もない)場合
If IsNull(MAX開始日付) Then
    WK単価 = 0
'MAX開始日付 の値が Null ではない場合
Else
    '[T9_商品単価マスタ] のレコードのうち、
    'フィールド[商品CD]の値がテキストボックス[商品CD]の値に等しく、
    'かつフィールド[開始日付]の値が MAX開始日付 に等しい
    'レコードの[単価]の値を取得する。
    WK単価 = DLookup("単価", "T9_商品単価マスタ", _
                     "[商品CD]=" & Me.商品CD & _
                     " And [開始日付] = #" & Format(MAX開始日付, "yyyy/mm/dd") & "#")
End If
 
------------------------------------------------------------------------
 
以上のようなコードを記述なさればよろしいでしょう。
 
もし同様の方式で[単価]の値を取得しなければならない場合が
他にもあるのならば、標準モジュールにユーザー定義関数を作成して
適宜呼び出すようにされることをお奨めします。

投稿日時: 18/07/17 10:09:02
投稿者: にーさん

skさん、おはようございます。
 
返事が遅くなりすみませんでした。
 
Dmax関数を使って「出荷日」より小さい最大値を求めるのですね。
言われてみれば納得です。
 
今まで長い間考えていたのが、嘘のように解決されました。
 
ありがとうございました。