Access (VBA)

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

 
(Windows 10全般 : Access 2016)
15日締めの期間算出方法
投稿日時: 19/06/12 11:48:26
投稿者: YN山

毎月15日締めで、利用月数を算出したいのですが、
どのような方法があるか、教えていただけないでしょうか。
 
累積テーブルには、以下の項目があります。
 
キー項目|開始日|終了日
 
半期毎に計算したく、下期は年をまたぎます。
例:前年下期、2018/09/16〜2019/03/15
  上期、2019/03/16〜2019/09/15
 
テーブルデータの開始日は、算出期間より過去の場合もあります。
終了日がブランクの場合は、(上期の場合は)2019/09/15として計算したく。
 
よろしくお願いいたします。

回答
投稿日時: 19/06/12 17:39:57
投稿者: sk

引用:
毎月15日締めで、利用月数を算出したい

引用:
テーブルデータの開始日は、算出期間より過去の場合もあります。
終了日がブランクの場合は、(上期の場合は)2019/09/15として計算したく。

ここでの「算出期間」とは具体的に何を意味しているのでしょうか。
 
例えば、計算を行なう都度「2019年度上期」のような
任意の期別を抽出条件として指定する仕組みを
想定しているのでしょうか。

投稿日時: 19/06/13 11:42:39
投稿者: YN山

sk様
お返事ありがとうございます。
 
ご指摘のところは、処理を開始すると、フォームがポップアップし、
年度と開始月のパラメータを手入力して、
半期の期間を自動設定できればと思っております。
 
よろしくお願いいたします。
 

投稿日時: 19/06/13 12:06:27
投稿者: YN山

上記の続きです、
開始16日、終了15日の日付けは固定にしたいので、
手入力させず、プログラムで追加したいと考えています。
 
Dim Res As Integer
 
Res = InputBox("処理開始年月を入力してください(半角数字)" + vbCr + "例:2019年4月→201904", "処理開始年月の入力")

回答
投稿日時: 19/06/13 14:05:14
投稿者: sk

引用:
処理を開始すると、フォームがポップアップし、
年度と開始月のパラメータを手入力して、
半期の期間を自動設定できればと思っております。

引用:
開始16日、終了15日の日付けは固定にしたいので、
手入力させず、プログラムで追加したいと考えています。

引用:
例:2019年4月→201904

"201905" や "201911" といった値が入力された場合の
「半期」の扱いはどのような形になるのでしょうか。
 
仮に、当年の3月16日から9月15日までを「上期」(4月期〜9月期)、
当年の9月16日から翌年の3月15日までを「下期」(10月期〜3月期)
と定義するとして、この時「上期」を表す数値を 1、
「下期」を表す数値を 2 として扱う場合は、
例えば次のようなパラメータクエリを作ればよいはず。
 
( SQL ビュー)
---------------------------------------------------
PARAMETERS [年度を入力] Long, 
           [期別を入力] Long;
SELECT [年度を入力] AS 年度, 
       Choose([期別を入力],"上期","下期") AS 期別, 
       DateSerial([年度を入力],Choose([期別を入力],3,9),16) AS [期首日], 
       DateAdd("m",6,[期首日])-1 AS [期末日], 
       [累積テーブル].[キー項目], 
       [累積テーブル].[開始日], 
       [累積テーブル].[終了日], 
       IIf([期首日]<[累積テーブル].[開始日],[累積テーブル].[開始日],[期首日]) AS [今期の利用開始日], 
       IIf(Nz([累積テーブル].[終了日],[期末日])<[期末日],[累積テーブル].[終了日],[期末日]) AS [今期の利用終了日], 
       DateDiff("m",[今期の利用開始日],[今期の利用終了日])-(Day([今期の利用開始日])<16)+(Day([今期の利用終了日])<16)+1 AS [今期の利用予定月数]
FROM [累積テーブル]
WHERE Not (   Nz([累積テーブル].[終了日],#9999/12/31#) < DateSerial([年度を入力],Choose([期別を入力],3,9),16) 
           OR [累積テーブル].[開始日] > DateAdd("m",6,DateSerial([年度を入力],Choose([期別を入力],3,9),16))-1);
---------------------------------------------------
 
上記のように年度内における各期の日付範囲を明確に区切るのではなく、
「指定された開始月を始期とする 6 ヶ月間」
(例えば "201905" という値がパラメータに入力された場合は
2019/04/16 から 2019/10/15 までの期間)における利用月数を
計算なさりたいのであれば、少し違った作り方をすることになるでしょう。
 
あとはパラメータへの値渡しをどのような形式で行なうか、
そして計算した結果をどのような形で出力するかの問題です。

投稿日時: 19/06/13 14:40:32
投稿者: YN山

sk様
クエリを作成いただき、ありがとうございます。
 
おっしゃる通り、途中の開始月が入力されることを想定できていませんでした。
(上期)3/16〜9/15、(下期)9/16〜3/15と、で固定ですので、
ご提供いただいたクエリでやりたい事を完ぺきにもうらしております。
クエリ1回でできるなんて感激しました。
 
もしよろしければ、

引用:
DateDiff("m",[今期の利用開始日],[今期の利用終了日])-(Day([今期の利用開始日])<16)+(Day([今期の利用終了日])<16)+1 AS [今期の利用予定月数]

 
引用:
WHERE Not ( Nz([累積テーブル].[終了日],#9999/12/31#) < DateSerial([年度を入力],Choose([期別を入力],3,9),16)
           OR [累積テーブル].[開始日] > DateAdd("m",6,DateSerial([年度を入力],Choose([期別を入力],3,9),16))-1);

の部分がどういった意味なのか、教えていただけないでしょうか。
私の理解力が足らず申し訳ございません。

回答
投稿日時: 19/06/13 17:48:46
投稿者: sk

引用:
DateDiff("m",[今期の利用開始日],[今期の利用終了日])

まず上記の DateDiff 関数は 2 つの日付の差を月単位で返すものですが、
それぞれの日付の「日」の部分を無視して計算した結果となります。
 
例えば 2018/12/31 と 2019/01/01 の差を求めるとして、
日数単位ではたった 1 日差( 30 日に満たない)ですが、
月数単位の計算では「日」を無視して 1 という結果が返されることになります。
 
(日数単位の差を求める場合)
---------------------------------------------------------
 
DateDiff("d",#2018/12/31#,#2019/01/01#)
 
  -> 1
 
---------------------------------------------------------
 
(月数単位の差を求める場合)
---------------------------------------------------------
 
DateDiff("m",#2018/12/31#,#2019/01/01#)
 
  -> 1
 
---------------------------------------------------------
 
引用:
+1

また、2018/12/01 と 2018/12/01 の時間差は当然 0 ですが、
利用を開始した当日/当月はカウントに含めなければなりませんので、
更に 1 を加える必要があります。
 
引用:
-(Day([今期の利用開始日])<16)

仮に、次のような 2 つのケースが存在するとします。
 
・A さんは 2019/04/01 から利用を開始し、
 2019/05/31 に利用終了した。
 
・B さんは 2019/04/16 から利用を開始し、
 2019/05/15 に利用終了した。
 
この時、2 人が利用を開始した月である 2019年4月 の
 1 ヶ月間(暦通りの意味での 2019/04/01 〜 2019/04/30 )に
着目すると、A さんのケースについては
2019/04/01 から 2019/04/15 までが「2019年度4月期」
(月期の初日は 2019/03/16 = 利用開始日の前月)、
2019/04/16 から 2019/04/30 までが「2019年度5月期」
(月期の初日は 2019/04/16 = 利用開始日当月)に
掛かる期間となり、「2019年4月」中における月期換算での
利用月数は「 2 期」という扱いとなるはずです。
 
一方、B さんのケースでは 2019/04/16 から 2019/04/30 までの
「2019年度5月期」(月期の初日は 2019/04/16 = 利用開始日の当月)のみですから、
「2019年4月」中における利用月数は「 1 期」という扱いになります。
 
つまり、暦の上では同じ月に利用を開始したケースでも、
開始した日がその月の 1 日から 15 日までの間である場合は
月期の初日をその月の前月扱いとして処理しなければならないため、
「日を無視した月数の差」に更に 1 を加える必要があります。
 
その処理を行なっているのが上記の式です。
(式の原理については必要があれば解説しますが、今は割愛します)
 
引用:
+(Day([今期の利用終了日])<16)

次に、2 人が利用を終了した月である 2019年5月 の
1 ヶ月間(暦の上では 2019/05/01 〜 2019/05/31 )に着目します。
 
このうち前半の 2019/05/01 から 2019/05/15 までは
「2019年度5月期」に含まれる期間ですが、
この期間に当たる月数は前月=「2019年4月」側の計算に
含まれています。
 
つまり「2019年5月」についての月数計算は、
後半の 2019/05/16 以降も利用が継続されているかどうか
(「2019年度6月期」分の有無)で判断すればよいため、
「2019年度5月期」の締日である 2019/05/15 以前に
利用が終了されていれば 0 期( B さんのケース)、
そうでなければ(「2019年度6月期」の)1 期
( A さんのケース)とカウントすることになります。
 
しかし、後者の「 1 期」は既に DateDiff 関数の結果に含まれている
(「2019年度6月期」の前半が無条件でカウントされている)ため、
利用を終了した日がその月の 1 日から 15 日までの間である場合は
1 を引くという計算を行なうことになります。
 
その処理を行なっているのが上記の式です。
(同じく原理は割愛します)
 
引用:
DateDiff("m",[今期の利用開始日],[今期の利用終了日])-(Day([今期の利用開始日])<16)+(Day([今期の利用終了日])<16)+1 AS [今期の利用予定月数]

ここまでの計算の流れを要約すると、次のようになります。
 
1. [今期の利用開始日]と[今期の利用終了日]の月数の差を
   (「日」を無視して)求める。
 
2. [今期の利用開始日]を含む月を「当月分」として更に 1 を加える。
 
3. [今期の利用開始日]の「日」が 16 未満である場合は
   更に 1 を加える
    
4. [今期の利用終了日]の「日」が 16 未満である場合は
   1 を引く。
 
以上の計算を A さんと B さんのそれぞれに適用すると、
A さんのトータル利用月数は「2019年度4月期」から「2019年度6月期」までの 3 期、
B さんのトータル利用月数は「2019年度5月期」の 1 期のみの利用、という結果が出ます。
 
引用:
WHERE Not ( Nz([累積テーブル].[終了日],#9999/12/31#) < DateSerial([年度を入力],Choose([期別を入力],3,9),16)
           OR [累積テーブル].[開始日] > DateAdd("m",6,DateSerial([年度を入力],Choose([期別を入力],3,9),16))-1);

上記の WHERE 句は、下記のいずれかの条件に該当するレコードを
抽出しないためのものです。
 
・[終了日]の値( Null の場合は 9999/12/31 として扱う)が
 指定された年度と期別における期首日よりも過去の日付である。
 (=指定された期別の前期以前に利用が終了しているレコード)
 
・[開始日]の値が、指定された年度と期別における期末日よりも
 未来の日付である。
 (=指定された期別の翌期以降から利用が開始されるレコード)

投稿日時: 19/06/13 19:26:00
投稿者: YN山

sk様
丁寧な解説をいただきありがとうございます。
理解するのに時間がかかり、遅くなりました。
 
-1(True)、0(False)の結果で計算しているということですね。
日付計算の考え方について、大変勉強になりました。
 
本件は解決しましたのでクローズいたします。
この度は本当にありがとうございました。