きなこSHOW

寝ながらあれこれ考える

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

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

11月は入って間もなく祝日があり、営業日をカウントする処理では開発者泣かせでもあります。
Accessには一連のWORKDAY関数群は用意されておらず、 AccessからExcelの組み込み関数を使う「Excel.Application」の機能はありますが、その場合「休日」セル範囲はどこに書いたらよいのやら、悩ましいところです。

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

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

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

こちらの関数は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)

実行結果

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

記述例(SQL)

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

実行結果

CHECKEIGYOBIを実行すると次のパラメータダイアログが表示されます。
 
結果は、0:休日が返ってきます。


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

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

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

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

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

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

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

準備

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

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

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

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

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

サンプルクエリ(SQL)

実行結果

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

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

なにかのお役に立てば。