パソコン情報

【Access】複雑な日付の計算(複数の締日など)

tigerlily713によるPixabayからの画像

 

Microsoft(マイクロソフト)のデータベースソフトAccess(アクセス)を使っていると、日付の計算をしなければならない時があります。

今回も消費税の仕組みを実装するのに、ちょっと複雑な日付計算が出てきましたので備忘録にしてみました。

 

 

締日を含んだ複雑な状況

 

1日から月末までの数値を合算して消費税を出すだけなら簡単。

 

●今月初日

DateSerial(Year(Date), Month(Date), 1)

 

●今月末

DateSerial(Year(Date), Month(Date) + 1, 0)

 

あとは、簡単であり、先月初めなら、月の部分を「Month(Date)-1」にすればいいし、来月初なら、「Month(Date)+1」にすればいい。

月末も同じ考えで、先月末なら、「Month(Date) 」とすればいいし、来月月末なら、「Month(Date)+2」というに足して行けばいい。 

 

つまり、あとはクエリーなので、Between関数を使って日付て抽出してやればOKですよね。

 


Between DateSerial(Year(Date), Month(Date), 1) and DateSerial(Year(Date), Month(Date) + 1, 0)

 

しかし、

 

ココがポイント

締日が、20日、25日、月末と3期間ある場合

 

はどうしたらいいのか?

 

 

3期間の締日の計算

 

答えは簡単ですが、クエリーなどで、「OR」を使って(実際は行を変えてOR状態にする)やれば3期間の抽出が可能ですよね。

 

●当月1日~月末


Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)

 

●20日締め(前月21日~当月20日)


Between DateSerial(Year(Date()), Month(Date())-1, 21) and DateSerial(Year(Date()), Month(Date()) , 20)

 

●25日締め(前月26日~当月25日)


Between DateSerial(Year(Date()), Month(Date())-1, 26) and DateSerial(Year(Date()), Month(Date()) , 25)

 

※Dateの部分をDate()に変更しないと今日の日付が出ませんでした。

 

 

でも、

 

Q. 上記のクエリーなら、前月21日~当月末までのクエリーにすればいいんじゃないですか?

 

A. 正にその通りなのです。

 

通常は、

 

ココに注意

メーカーによって締日が変化する

 

のですから、その締日によって計算が変わってくれなければ意味がないですよね。

 

 

月次のマスターテーブルから計算

 

月次の為のマスターテーブルが他にある場合にどのようにしたら良いのかを考えてみました。

 

この例では、作業中」というのが当月処理している月になるので、まず、テーブルから月次処理年月日を引っ張ってくるところから始めます。テーブル名は、「dbo_mst_月次」としてあります。

 

DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'")

 

※作業中のカンマに注意してください。

https://pctips.jp/pc-soft/access-dlookup-howto2019/

 

 

 

 

計算するテーブル元には、「締日」というフィールドがあり、20、25、31という数字でメーカー毎に設定してあるという事を前提にします。

それをクエリーで期間選択するには、下記のように設定すると締日ごとに期間が変化して抽出されるようになりました。

 

 

Between DateAdd("d",[締日],DateAdd("m",-1,DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'"))) And DateAdd("d",[締日]-1,DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'"))

 

 

消費税の差額を計算

 

ココに注意

2019年の10月から消費税が10%になります

 

から、締日が違うという事は、その差額を計算したいという要望が出てきます。

 

つまり、10月になって経理上、20日締めの場合は、

9月21日~10月20日の請求分から9月21日~9月末までは、8%で計算しなければならないのです。

 

 

私が考える方法としては、

 

ココがポイント

全てを10%で計算して、差額の2%分を引く

 

という方法で消費税対策をしてみたいと考えています。

 

計算式としては、10月が当月だとすれば、

 

ココがポイント

前月(締め日+1)~前月末までの消費税額

 

を産出できればいいという事になります。

 

Between DateSerial(Year(DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'")),Month(DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'")-1),[締日]+1) And DateSerial(Year(DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'")),Month(DLookUp("月次処理年月","dbo_mst_月次","終了確認 = '作業中'")),0)

 

もう、訳が分からなくなるくらいに複雑ですよね。

自分の備忘録として残しておきたいと思います。

 

 

DateSerialとDateAdd

 

今回の処理で、この2つの関数の違いが混乱しました。

DateSerialの方が良く使われるようですが、DateAddの方がシンプルで分かり易かったりします。

 

例えば、X=「2019/09/01」だとして、「2019/09/21」にしたい場合、

 

●DeteSerial(Year(X),Month(X),21)

●DateAdd("d",20,X)

 

という感じで同じことができるんじゃないかと思うのです。

しかし、月末を出したい場合になるとDateSerialなら

 

●DeteSerial(Year(X),Month(X)+1,0)

※最後の「0」が前日の意味になるらしい。

 

ココに注意

DateAddの方は、月頭や月末がうまく出せない

 

ように思えます。