きなこSHOW

VBAと日々の戯言

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

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

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

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

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

VBA応用(「営業日数」の算出)|Excelでお仕事!

kinaccco.hatenadiary.com

こちらの関数はExcelだけでなく、なんとAccessから呼び出すことも可能。

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

Accessで営業日関数を使えるようにする下準備

この関数は次の手順でAccessのデータベースに追加します。

  1. 井上さんの営業日算出関数をコピーして、いったんテキストエディタなどに貼り付けておきます。

    (ここはあくまで仮置き場で、次の工程で作成する新規標準モジュールに直接貼り付けても大丈夫です)
  2. AccessのVBEで標準モジュールを作成します。

    VBEを開き、メニューバー[挿入]-[標準モジュール]をクリックします。

  3. コピーした関数を、Accessの標準モジュールに貼りつけます。

    上の図が貼り付けた状態。
  4. モジュールに適当な名前を付けて保存します。

以上で営業日モジュールの追加は完成です。

このようにVBAのコードを標準モジュールに保存しておくと、
モジュール(VBA)やクエリから呼び出す事ができるようになります。

では、VBAやクエリで実際にこの関数を使ってみましょう。 

VBAから呼び出す方法

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

書式

CHECKEIGYOBI(日付)

記述例

MsgBox CHECKEIGYOBI("2016/11/03")

実行結果


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

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

書式

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

記述例

MsgBox SUMEIGYOBI("2016/11/01", 3)

実行結果

SUMEIGYONISSU・・・2つの日付間の営業日数を返す関数

書式

SUMEIGYONISSU(日付FROM, 日付TO)

記述例

MsgBox SUMEIGYONISSU("2016/10/01", "2016/11/01")

実行結果

この3種の関数の引数は日付型ですが、VBAで暗黙の型変換のような機能が働いているのかな??
文字列を渡しても日付に変換できる値であれば、上の例のようにどう見ても文字列な値を渡しても、日付とみなして計算してくれます。

クエリから呼び出す方法

クエリでの記述方法も、VBAとたいして変わりません。

CHECKEIGYOBI

書式(SQL)

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

書式(クエリデザイナのグリッド内)

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

SUMEIGYOBI

書式(SQL)

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

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

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

SUMEIGYONISSU

書式(SQL)

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

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

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

クエリ実行例

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

テーブル名:T_受注

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

記述例(SQL)

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

実行結果

テーブルなしのクエリの例

記述例(SQL)

SELECT [YYYY/MM/DD:日付を入力]
     , CHECKEIGYOBI([YYYY/MM/DD:日付を入力]) AS [営業日だったら1]
SELECT DATE() As [今日の日付]
     , [9999:日数を入力]
     , SUMEIGYOBI(Date(),[9999:日数を入力]) AS [入力した営業日数経過後の日付]
SELECT DATE() As [今日の日付]
     , [YYYY/MM/DD:日付を入力]
     , SUMEIGYONISSU(Date(),[YYYY/MM/DD:日付を入力]) AS [入力した日付までの営業日数]

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

実行結果

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

結果は、0:休日が返ってきます。


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

試しに、2を入力すると、
2営業日後の加算結果はこうなります。

今回の例では土日が除外されてこのような結果に。

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

12月1日までの営業日数はこうなります。

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

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

準備

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

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

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

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

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

サンプルクエリ(SQL)

INSERT INTO T_カレンダー ( 年月, 日付, 営業日数)
SELECT [A].年月
     , [A].日付
     , [A].営業日数
  FROM (
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 1) AS [日付]
         , 1 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 2) AS [日付]
         , 2 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 3) AS [日付]
         , 3 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION 
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 4) AS [日付]
         , 4 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 5) AS [日付]
         , 5 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 6) AS [日付]
         , 6 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 7) AS [日付]
         , 7 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 8) AS [日付]
         , 8 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 9) AS [日付]
         , 9 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 10) AS [日付]
         , 10 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 11) AS [日付]
         , 11 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 12) AS [日付]
         , 12 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 13) AS [日付]
         , 13 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 14) AS [日付]
         , 14 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 15) AS [日付]
         , 15 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 16) AS [日付]
         , 16 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 17) AS [日付]
         , 17 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 18) AS [日付]
         , 18 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 19) AS [日付]
         , 19 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 20) AS [日付]
         , 20 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(DateSerial(LEFT([B].[年月],4), MID([B].[年月],5,2), 1), -1), 21) AS [日付]
         , 21 AS [営業日数]
      FROM T_CONTROL AS [B]
    UNION
    SELECT [B].[年月] AS [年月]
         , SUMEIGYOBI(SUMEIGYOBI(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].営業日数

実行結果

T_カレンダーに次のレコードが追加されます。

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

どこかでお役に立てば。