2004 年 2 月 9 日
執筆 : Sal Ricciardi
日付と時刻はほとんどのデータベース アプリケーションで重要な役割を担います。この第 1 回目のコラムでは、タイムシート アプリケーションでよく使用する処理に関連して、Access で日付/時刻値がどのように格納されるか、そして経過時間がどのように計算されるかについて説明します。この後サンプルとして用いる 3 つの関数が定義されたサンプル データベースがダウンロードできるようになっているので、ぜひご利用ください。
| 対象アプリケーション |
|---|
Microsoft Office Access 2003
Microsoft Access 2000 および 2002 |
Office のコラムをすべて表示する
Access に関して新しいやり方を学ばなければならない状況はどれ位あるのでしょうか? もしあなたが Access データベースを日常的に操作したり作成したりする立場にあるのであれば、その機会は "たくさん" あります。これが、この「Access を活用する」を私たちが書いている理由です。このコラムは、読者の皆さまから寄せられた Access に関連する質問にお答えする形の定期コラムです。私たちの目標は単純です。それは、読者が日々直面する Access の問題に対して実際に役立つ解決策を示すことです。そこで、何か知りたいことがある方は、msaccess@microsoft.com までぜひ質問をお寄せください。お送りいただいた質問の中から適当なものを私が選びます。このコラムは、読者の皆さんの積極的な参加で成り立っています。
世界には膨大な数の Access ユーザーがいることをご存じですか? これだけの知識を有効に使わない手はありません。他の Access ユーザーと分かち合いたいヒントやテクニックを見つけたら、msaccess@microsoft.com までお気軽に情報をお寄せください。皆さまの投稿の中から、特に便利で独創的な情報を定期的に掲載します。
データベース アプリケーションにおける基本的な処理の 1 つに、日付と時刻を追跡する処理があります。Access では、日付/時刻データ型を使って日付と時刻の処理を実行します。最初の手順としては、どのようなサブジェクトに基づくテーブルが必要かを決定します。次に、そのテーブルの中でどのようなフィールドが必要かを決定します。そして、それぞれのフィールドに対してデータ型を選択します。Access では基本となるデータ型を多数サポートしています (たとえば、テキスト型、メモ型、数値型、通貨型、オートナンバ型、Yes/No 型、OLE オブジェクト型、ハイパーリンク型、そして最後に日付/時刻型)。
日付/時刻型は、基本的なデータ型です。日付/時刻型を使用してカレンダーの日付と時刻を格納することによって、それに関連する Access の豊富な機能を利用できます。たとえば、日付について算術計算を実行したり、売掛金を処理したりするために請求書の日付から何日が経過したかを計算したりできます。また、データの入力規則が組み込まれているので、2/31 のような無効なデータをユーザーが入力できないようになっています。
日付/時刻型フィールドで使用されるデータ型の例としては、請求書の日付、注文の入力日、タイム スタンプ、従業員の雇用日、開始時刻と終了時刻、タイムカードの出勤時刻および退出時刻が挙げられます。
ときには、Access に表示される日付または時刻の書式を指定する場合があります。そのような場合は、テーブルにフィールドを作成し、フィールドの [書式] プロパティ (プロパティ : コントロール、フィールド、またはオブジェクトの名前付き属性。オブジェクトの特性 (サイズ、色、画面位置など) や状態 (表示/非表示など) を定義します。)に表示書式を入力できます。このとき、多数用意されている定義済みの書式から 1 つを選択することも、または月、日、年、時、分などの位置を正確に指定した独自の書式を作成することもできます。
日付/時刻値の使い方に関してよく受ける質問の 1 つに、経過時間を計算して、その結果に適切な書式を設定するにはどうしたらよいかという質問があります。ここでは、2 つの解決策を示しますが、その解説に進む前に、Access で日付/時刻値がどのように格納されるのかを確認しましょう。
日付/時刻値の格納方法
Access では、日付/時刻値は内部的に倍精度 (倍精度: 精密度の低い (単精度) 数値を保存するために必要なコンピュータのメモリの 2 倍 (2 文字、通常 8 バイト) を保存する数値の属性。浮動小数点形式で表現するコンピュータで通常処理されます。)浮動小数点値として格納されます。それぞれの値は、日付成分と時刻成分を含んでいます。小数点の左側の整数部は日付を、小数点の右側の小数部は時刻を表します。
この図に示す倍精度値は、2003 年 12 月 24 日の 9:00 PM を 37979.875 として表しています。このように表示されるのは、Access が、1899 年 12 月 30 日を基準日として、この日から経過した日数を日付成分として格納するからです。したがって、基準日 (1899 年 12 月 30 日) から 2003 年 12 月 24 日までの間に 37,979 日が経過していることがわかります。
日付成分の負の値は、基準日よりも前の日付を表します。たとえば、日付成分の値が -1 であれば、基準日の 1 日前、つまり 1899 年 12 月 29 日を表します。時刻成分は、1 日を 24 時間で表した場合の割合です。したがって、.875 という時刻成分は 21 時間を表し、9:00 PM に対応します。
Access で内部的に日付/時刻値を表す方法 (上記の例の 37979.875) は、通常私たちが日付/時刻を扱っている方法とは異なるので、内部の倍精度形式の日付/時刻値を、より身近な形式に変換できるようにしておく方が都合がよいと言えます。これを行うには、Access の CDate() 組み込み関数を使います。
関数とは一連の処理を実行して、多くの場合、結果を返す手続きです。Access にはたくさんの組み込み関数が用意されており、これらをデータベース アプリケーション内で使用して、計算を実行したり、このケースのようにデータ型を変換したりすることができます。CDate() 関数は、倍精度値を日付/時刻値に変換します。CDbl() 関数を使用すると、日付/時刻値を、対応する倍精度値に変換する逆の処理を実行することができます。
経過時間を計算する
経過時間の計算は簡単です。Access は、2 つの日付間の時間間隔を、それが数か月または数年にわたる長期の間隔であっても計算できます。ただし、結果の値を自分の好きなように書式設定して表示するためには、ある程度の創造性と、Access に採用されているプログラミング言語である Microsoft Visual Basic® for Applications (VBA) に関する知識が必要になります。
以下に示す HoursAndMinutes 関数は、時間間隔を "時:分" 形式で書式設定するカスタム関数です。この関数は、週次タイムシート レポートで合計が求められる場合などに、累積時間を正確に扱えるよう設計されています。この関数に時間間隔または日付/時刻の計算式 (たとえば、#12/24/2003 11:00PM# - #12/10/2003 6:00AM#) を渡すと、関数は適切に書式設定された文字列 (この場合は 353:00) を返します。明示的な日付/時刻文字列を指定するには、日付部分と時刻部分をシャープ記号 (#) で囲みます。リテラル文字列を指定する場合は、意味を明確にしてミスをさけるために、日付成分と時刻成分の両方を指定するようにしてください。
Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' 時:分の文字列として書式設定された時間間隔を返す
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function
hours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 時間 * 60 分
minutes = totalminutes Mod 60
totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 秒
seconds = totalseconds Mod 60
If seconds > 30 Then minutes = minutes + 1 ' 分を切り上げ、
If minutes > 59 Then hours = hours + 1: minutes = 0 ' 時を調整する
HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function
HoursAndMinutes 関数は、時、分、および秒のそれぞれの部分を計算する、つまり、間隔の個々の成分を取り出すことで動作します。ある日付/時刻値から別の日付/時刻値を引いて間隔を求めると、その結果は、小数点の左側に日数を表す値、右側に 1 日のうちの割合を示す値を持つ倍精度値となります。時成分を求めるには、間隔 (interval) に 24 を掛けます。分成分を求めるには、最初に 1 日を分に換算した数である 1,440 を interval に掛けます。これで、interval に相当する分の合計数が求められます。次に、この値を 60 で割って、hours の結果値に含まれている分の値を取り除くと、残りの値が私たちが求めている値になります (この計算には Mod 演算子を使います)。同様の方法を使って日数の合計を求めた後に除算を行って剰余を求めることで、秒成分 (seconds) が得られます。
関数の残りの部分は、結果を時と分に切り上げる処理と結果文字列を生成する処理から構成されます。
ときには、もっと話し言葉に近くて読みやすい結果文字列を生成する場合があります。次に示す例は、2 つの日付/時刻値を受け取り、"10 days, 20 hours, 30 minutes, 40 seconds" のような長い文字列として経過時間を返します。
Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'**********************************************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' 開始日付/時刻から終了日付/時刻の間の経過時間を
' 次のように書式設定した文字列として返す :
' "10 days, 20 hours, 30 minutes, 40 seconds".
'**********************************************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")
' 文字列の日の部分
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' 文字列の時の部分
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' 文字列の分の部分
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' 文字列の秒の部分
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function
この ElapsedTimeString 関数は、最初に終了時刻から開始時刻を引いて時間間隔を求めます。次に、Format 関数を使って、日、時、分、および秒の各成分を決定します。そしてこの関数は、単数の結果 ("Days" ではなく "Day") を表して、"0 seconds" のような不必要な成分を取り除くことで、目的に合うようにそれぞれの成分を組み合わせて結果文字列を作成します。
関数を作成する
このコラムで取り上げたサンプル データベースをダウンロードすることができます。データベースを開いて Elapsed Time VBA コード モジュールを見ると、HoursAndMinutes 関数と ElapsedTimeString 関数が使用されていることがわかります。自分で関数を作成するには、Microsoft Access を起動し、次の手順に従います。
- [ファイル] メニューの [開く] をクリックします。次に、関数を挿入するデータベースをクリックします。
- データベース ウィンドウで、[モジュール] をクリックします。
- [新規作成] をクリックして新しいモジュールを作成します。
- ここに記載されているとおりに HoursAndMinutes 関数と ElapsedTimeString 関数を入力します。または、サンプル データベースをダウンロードしてある場合は、Elapsed Time モジュールからこの 2 つの関数をコピーして貼り付けます。
- [ファイル] メニューの [上書き保存] をクリックします。
- モジュール名 (たとえば Elapsed Time) を入力し、Enter キーを押します。
- [デバッグ] メニューの [<データベース名> のコンパイル] をクリックします。
- [ファイル] メニューの [終了して Microsoft Office Access へ戻る] をクリックします。
関数をデータベースに組み込むと、クエリ、フォーム、レポートなどの Access オブジェクトにおいてその関数を使用できるようになります。
クエリ、フォーム、およびレポートで経過時間を使用する
それぞれの製品の注文から出荷までの時間を調べたいとします。そこで、Invoices テーブルと Customers テーブルを使用するクエリを作成します。Invoices テーブルには、OrderDate フィールドと ShippedDate フィールドが含まれています。これらのフィールドは、製品がいつ注文されていつ出荷されたのかがわかるよう、その値を ElapsedTimeString 関数に渡すのに必要となります。Customers テーブルには CustomerName のような情報が格納されていて、各注文の発注元である顧客を識別できるようになっています。
TimeToShip 計算をクエリに追加するためには、クエリ グリッドに式を追加する必要があります。式とは、演算子 (たとえば = や + )、コントロール名、フィールド名、1 つの値を返す関数、および定数値を組み合わせたものです。たとえば、TimeToShip 計算を追加するためには、クエリ グリッドの新しい列の [フィールド] セルに次の式を挿入します。
TimeToShip: ElapsedTimeString([OrderDate],[ShippedDate])
この式は、2 つのことを示しています。1 つ目は、結果の列のタイトルを "TimeToShip" とすることです。2 つ目は、パラメータとして OrderDate と ShippedDate を渡した ElapsedTimeString 関数の評価結果をこの列に含めることです。このクエリを処理するとき、Access はクエリ結果のすべての行に対して式を評価します。結果は次のようになります。
OrderDate フィールドと ShippedDate フィールドには日付/時刻値が格納されていますが、実際には時刻は記録されていません。時刻は、既定で 00:00:00、つまり真夜中の 12:00 に設定されます。したがってこの既定の設定が適用される限り、出荷までの時間の値は必ず日数となり、時、分、および秒の値は必要でなくなります。日数だけで十分な場合は、次に示す ElapsedDays 関数を使用することができます。この関数は、ElapsedTimeString 関数から不必要なコードを取り除いたものです。
Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
'****************************************************************************************
' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
' 開始日付/時刻から終了日付/時刻の間の経過日数を
' 次のように書式設定した文字列として返す:
' "10 days" または "1 day".
'****************************************************************************************
Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function
ここで、経過時間をフォームまたはレポートに表示するケースを考えてみましょう。最初に、フォームまたはレポートのデザイン領域にテキスト ボックス コントロールを配置します。コントロールとは、データを表示したり、アクションを実行したり、またはフォーム/レポートに装飾を施すオブジェクトです。テキスト ボックス コントロールは、テキストを入力したり、計算結果などのテキストを表示したりすることができるコントロールです。
TimeToShip 計算をレポートに追加するには、テキスト ボックスの "コントロールソース" プロパティに式を追加します。プロパティとはコントロールに関連付けられている属性のことで、Access には各種のプロパティが用意されています。"コントロールソース" プロパティは、コントロールのデータをどこから取得するかを Access に伝えるプロパティです。経過時間をテキスト ボックスに表示するには、テキスト ボックスの "コントロールソース" プロパティに次の式を指定します。
=ElapsedTimeString([OrderDate],[ShippedDate])
ここで、式の先頭の等号 (=) に注目してください。"コントロールソース" プロパティを設定する場合は等号が必要です。等号は、フィールド名ではなく式が指定されていることを Access に通知するための記号です。
次に、全従業員の勤務時間を何時間何分という形で記録するタイムシート フォームを作成するケースを考えてみます。このケースでは、TimeIn フィールドと TimeOut フィールドを表示するフォームを作成します。この場合、1 日に働いた時間を分単位まで計算する必要があります。この計算には TimeandMinutes 関数の使用が最適です。最初に、"HoursWorked" ボックスを作成し、その "コントロールソース" プロパティに次の式を入力します。
=HoursAndMinutes([TimeOut]-[TimeIn])
ElapsedTimeString 関数と HoursAndMinutes 関数を使用したサンプルフォームとレポートの出力は、次のようになります。
TimeSheet フォームは、実際は Employees テーブルにリンクされている外側の TimeSheet フォームと、TimeSheet テーブルにリンクされている内側の TimeSheet サブフォームから構成されています。TimeSheet フォームに表示される [Total] 列を作成するには、まず TimeSheet サブフォーム上にテキスト ボックスを作成し、名前 (たとえば "SummedHoursMinutes") を付けます。次に、このテキスト ボックスの "コントロールソース" プロパティで次のように指定します。
=HoursAndMinutes(Sum([TimeOut]-[TimeIn]))
次に、外側の TimeSheet フォームに "Total" テキスト ボックスを作成し、その "コントロールソース" プロパティを SummedHoursMinutes に設定します。
=[TimeSheet Subform].Form!SummedHoursMinutes
サンプル データベースをダウンロードする
サンプル データベースには、このコラムで説明した関数が定義されている Elapsed Time モジュール、サンプル フォームとクエリ、サンプル レポート、および Access に付属する Northwind.mdb サンプル データベースから取り出したサンプル データが含まれています。
次回の話題
次回の話題には何を取り上げたらよいでしょう? ご意見、ご感想、ご質問、役に立つテクニックを msaccess@microsoft.com までお寄せください。
関連情報
「時間は人間が消費し得るものの中で最も貴重なものだ」という言葉はテオフラストスの好きな表現である。 - Diogenes Laërtius
著者について
Sal Ricciardi は、Microsoft Office ユーザー アシスタント チームのメンバーです。
