きなこSHOW

自作プログラム置き場です。

トップ > 営業日算出 > AccessVBAやクエリで営業日を計算する関数を紹介します

AccessVBAやクエリで営業日を計算する関数を紹介します

営業日算出 Access VBA クエリ 関数

Accessで営業日を計算したいです。
それはもう、Excelのとき以上に、計算したい...
にもかかわらず、AccessにはExcelのようなWORKDAY関数は用意されていません。
でも大丈夫！Accessで営業日の計算ができる関数とその使い方を紹介します。

ハッピーマンデーや改元関連の祝日変動などで休日を固定で管理できなくなり、営業日を扱う処理は開発者泣かせです。
AccessからExcelの組み込み関数を使う「Excel.Application」の機能はありますが、その場合「休日」セル範囲をどこに書いたらよいのやら。シートありきのWORKDAY関数群をAccessから使うことはできません。たいへん悩ましいところです。

Excelでお仕事！の営業日関数はAccessでも仕事してくれます

先日紹介させていただきました、「Excelでお仕事！」の井上さんの関数は
VBAで会社の営業日を計算してくれます。

VBA応用(年間カレンダー作成(祝日パラメータシートを使わない方法))

kinaccco.hatenadiary.com

こちらの関数はExcelだけでなく、なんとAccessから呼び出すことも可能。
VBAから実行することはもちろん、クエリから呼び出すことだってできる神モジュールなのです。

Excelでお仕事！営業日関数をAccessで使用する手順

こちらのサイトでは原則として、営業日カレンダーをシートに展開する方式のモジュールの使用を推奨されています。
Accessで使用することは想定されていないと思いますので、ご利用は自己責任でお願いいたします。

サンプルをダウンロードする

(1) 次のリンクからサンプルをダウンロードしてください。
www.asahi-net.or.jp
「年間カレンダーの作成」の図をクリックすると、サンプルをダウンロードすることができます。
保存したのち、zipを展開します。

Excel 側の作業

(2) 展開された3つのマクロBookの中から「05_SumEigyoNissu1R.xlsm」をExcelで開きます。

f:id:kinacco:20190425200851j:plain

(3) VBEに切り替えて、2本の標準モジュールと、1本のクラスモジュールをエクスポートします。

f:id:kinacco:20190424203313j:plain

modAboutCalendar2R.bas
modCalendarForFomula1.bas
clsAboutCalendar2R.cls
この3ファイルのエクスポートが済んだら、Excelはもう使わないので終了します。

Accessにモジュールをインポートする

(4) AccessのVBEでプロジェクトを右クリック→[ファイルのインポート]をクリックします。

f:id:kinacco:20190424192047j:plain

(5)「ファイルのインポート」ダイアログで、先ほどExcelからエクスポートしておいた3つのファイル(標準*2、クラス1)をインポートします。

f:id:kinacco:20190424203541j:plain
3ファイルまとめて一度にインポートしたいところだが無理っぽい(2019年4月現在)ので、地道に1ファイルずつインポートするしかないです。

f:id:kinacco:20190424203649j:plain

ぜんぶインポートし終わったのが上の図。
名前を付けて保存ダイアログが表示されるので、保存します。
これでAccessへの実装は完了です。

このようにVBAのコードをプロジェクトに実装すると、
モジュール(VBA)やクエリから呼び出す事ができるようになります。

では、AccessのVBAやクエリで実際にこの関数を呼び出してみましょう。

Accessでの呼び出し例

VBAから呼び出す方法

CHECKEIGYOBI2・・・引数に与えた日付が営業日か休日かを判定する関数

書式

CHECKEIGYOBI2(日付)

記述例

MsgBox CHECKEIGYOBI2("2019/04/30")

実行結果


戻り値の意味は、０は休日、1は営業日です。

SUMEIGYOBI2・・・日付と営業日数を加算して返す関数

書式

SUMEIGYOBI2(日付,加減算する日数)

記述例

MsgBox SUMEIGYOBI2("2019/04/26", 2)

実行結果

f:id:kinacco:20190425205143j:plain

SUMEIGYONISSU2・・・２つの日付間の営業日数を返す関数

書式

SUMEIGYONISSU2(日付FROM, 日付TO)

記述例

MsgBox SUMEIGYONISSU2("2019/04/26", "2019/05/07")

実行結果

f:id:kinacco:20190425205752j:plain

この3種の関数の引数は日付型ですが、日付リテラル値で指定しても、ダブルクォーテーションで囲った方法でも、どちらも正しく動作します。
VBAで暗黙の型変換のような機能が働いているのかな？？
文字列を渡しても日付に変換できる値であれば計算してくれます。

クエリから呼び出す方法

AccessVBAのPublicプロシージャはクエリから実行できます。
クエリでの記述方法も、VBAとたいして変わりません。

CHECKEIGYOBI2

書式(SQL)

CHECKEIGYOBI2(日付フィールド) AS [営業日フラグ]

書式(クエリデザイナのグリッド内に記述する場合)

[営業日フラグ]:CHECKEIGYOBI2(日付フィールド)

SUMEIGYOBI2

書式(SQL)

SUMEIGYOBI2(日付フィールド, 日数) AS [営業日付]

書式(デザイングリッド内)

[営業日付]:SUMEIGYOBI2(日付フィールド, 日数)

SUMEIGYONISSU2

書式(SQL)

SUMEIGYONISSU2(日付フィールドFROM, 日付フィールドTO, 0) AS [営業日数]

書式(デザイングリッド内)

[営業日数]:SUMEIGYONISSU2(日付フィールドFROM, 日付フィールドTO)

クエリ実行例

例として以下のようなテーブルを用意しました。

テーブル名：T_受注

てきとうなデータが入っているものとする。

f:id:kinacco:20190425211221j:plain

記述例(SQL)

SELECT [A].受注日
     , CHECKEIGYOBI2([A].受注日) AS [受注日は営業日なのか]
     , SUMEIGYONISSU2([A].受注日, [A].[指定納期]) AS [受注日から指定納期までの営業日数]
     , SUMEIGYOBI2([A].受注日, 3) AS [受注日の3営業日後]
  FROM T_受注 AS [A]

実行結果

2019年は4月26日から5月7日までの間の営業日数が2日なので正しく計算されている事が分かります。

f:id:kinacco:20190425211433j:plain

「指定納期」が空欄(NULL)のレコードは、SUMEIGYONISSU2の結果がエラーとなるのも正しい動きです。

パラメータを使用したテーブルなしのクエリの例

パラメータを使ってFROMのテーブルなしで営業日関数を呼ぶ例です。
もう趣味の領域。

◆営業日かどうかを判定するSQL

SELECT [YYYY/MM/DD:日付を入力]
     , CHECKEIGYOBI2([YYYY/MM/DD:日付を入力]) AS [営業日だったら1]

◆実行結果
CHECKEIGYOBI2を実行すると次のパラメータダイアログが表示されます。
f:id:kinacco:20190425214111j:plain
2019/5/1を指定した場合、結果は、0：休日が返ってきます。

f:id:kinacco:20190425214218j:plain

◆指定した営業日数を今日の日付に加算して表示するSQL

SELECT DATE() As [今日の日付]
     , [9999:日数を入力]
     , SUMEIGYOBI2(Date(),[9999:日数を入力]) AS [入力した営業日数経過後の日付]

◆実行結果
SUMEIGYOBI2を実行すると次のパラメータダイアログが表示されます。

f:id:kinacco:20190425214406j:plain

ためしに3を入力すると、3営業日を加算した結果はこうなります。

f:id:kinacco:20190425214501j:plain

◆指定した日付までの営業日数を表示するSQL

SELECT DATE() As [今日の日付]
     , [YYYY/MM/DD:日付を入力]
     , SUMEIGYONISSU2(Date(),[YYYY/MM/DD:日付を入力]) AS [入力した日付までの営業日数]

◆実行結果
SUMEIGYONISSU2を実行すると次のパラメータダイアログが表示されます。

f:id:kinacco:20190425220831j:plain
2019/4/25から5/7の間の営業日数はこうなります。
f:id:kinacco:20190425220927j:plain

 

応用編

営業日カレンダー(テーブル)を作成するクエリ

Accessクエリで「営業日カレンダー」テーブルを作成するSQL文を紹介します。
ここからは完全に趣味の領域。

準備

次のテーブルを用意します。
テーブル名：T_CONTROL

年月フィールド：テキスト型(6文字)

この「T_CONTROL」の「年月」フィールドには、タネになる年月として
次の値を入力しておくこととします。

f:id:kinacco:20190425222741j:plain

テーブル名：T_カレンダー

年月フィールド：テキスト型(6文字)
日付フィールド：日付/時刻型
営業日数フィールド：数値型(長整数型)
T_カレンダーは、レコード０件、空っぽでOK

サンプルクエリ(SQL)

INSERT INTO T_カレンダー ( 年月, 日付, 営業日数)
SELECT [A].年月
     , [A].日付
     , [A].営業日数
  FROM (
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 1) AS [日付]
         , 1 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 2) AS [日付]
         , 2 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 3) AS [日付]
         , 3 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 4) AS [日付]
         , 4 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 5) AS [日付]
         , 5 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 6) AS [日付]
         , 6 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 7) AS [日付]
         , 7 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 8) AS [日付]
         , 8 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 9) AS [日付]
         , 9 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 10) AS [日付]
         , 10 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 11) AS [日付]
         , 11 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 12) AS [日付]
         , 12 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 13) AS [日付]
         , 13 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 14) AS [日付]
         , 14 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 15) AS [日付]
         , 15 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 16) AS [日付]
         , 16 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 17) AS [日付]
         , 17 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 18) AS [日付]
         , 18 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 19) AS [日付]
         , 19 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 20) AS [日付]
         , 20 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 21) AS [日付]
         , 21 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI2(SUMEIGYOBI2(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 22) AS [日付]
         , 22 AS [営業日数]
      FROM T_CONTROL AS [B]) AS [A]
 WHERE [A].年月 = LEFT(REPLACE(CSTR([A].日付),"/",""),6)
 ORDER BY [A].営業日数

◆実行結果

カレンダーテーブルには次のように営業日だけが追加されます。

f:id:kinacco:20190425223139j:plain

毎月「T_CONTROL」の年月を変えてこのクエリを実行すれば、
営業日カレンダーが簡単に作成できる変態SQLサンプルでした。
いや、これって簡単って言えるのか...