MS Access Tips/Sample and VBA and Blog customize etc...

クエリでの期間の抽出条件

パラメータで[開始期間]と[終了期間]というように入力させて、その期間内に含まれる日付/時刻型のデータを抽出したい。
あるいは、抽出対象の方も[開始日時]、[終了日時]というように幅があり、期間が一部でも重なれば抽出したい。
というようなことはよくあると思います。

今回は、期間に関する抽出条件のあれこれを、考察してみましょう。

SQLビューのチャプター

難易度:

日付を対象、抽出条件は期間

パラメータ([開始日], [終了日])で期間を入力して日付/時刻型のフィールド(Fdateとします)を抽出対象とする場合は下記のようにSQLになります。

あるいは、

両者の違いは、前者は、[開始日]が[終了日]より大きい場合は抽出結果は0件になりますが、後者は、その場合でも[終了日]から[開始日]までのデータが抽出されます。

PARAMETERS宣言で、パラメータのデータ型と入力順を設定できます。

PARAMETERS宣言がないと、SQL文内で現れた順にパラメータが表示されますが、PARAMETERS宣言があるとそこで記述した順が優先されます。

また、指定したデータ型と合致しない値を入力すると再入力を要求されます。PARAMETERS宣言がないと、全て入力後にエラーメッセージがでます。日付を入力する場合、うっかり 2010/02/29 などと存在しない日付を入力してしまう場合があるので指定しておいた方がいいでしょう。

クエリのデザインビューでは、ウィンドウのテーブル表示部分の背景部分を右クリックして[パラメーター…]をクリックすると設定画面が現れます。

クエリパラメータ画面

フィールドのデータに時刻も含まれる場合、上記の式では、終了日時 は、0時(0:00:00)のデータしか抽出されないので注意が必要です。

例えば、

テーブル1
ID日時
12010/12/01 8:00
22010/12/01

というデータでWHERE句に、
日時 Between #2010/11/01# And #2010/12/01#
と設定した場合、ID 2 のレコードしか表示されません。

このように時刻も含まれてるデータで、終了日のすべてのデータも抽出するには、下記のように、終了日に1日足してイコールのない不等号(未満)で比較します。

BetWeen を使う場合は、下記のようになります。

パラメータの[開始日][終了日]の入力を片方でも省略すると抽出結果は0件になりますが、[開始日]だけを入力した場合は開始日以降のすべてのレコードを表示、[終了日]だけ入力した場合は、終了日以前のすべてのレコードを表示したいという要望がある場合もありますね。そんなときは下記のようにするといいでしょう。

Nz関数で、未入力(Null)の時はありえないほど小さい値、あるいは大きい値を返すようにします。

対象は期間、抽出条件も期間

両方とも期間だと両者の関係には6パターンあります。
言葉では説明が面倒なので、下図を参照してください。

期間の重なりのパターン

抽出条件の期間に対して、内部に含まれる期間データのみ抽出という場合は、図のDのパターンのみなので簡単です。
(フィールド名は、Date1, Date2 とします。また、Date1 <= Date2 とします。)

抽出条件の期間に対して、一部でも重なる期間データを抽出という場合は、図では、B, C, D, E の4パターンになります。素直に、すべてのパターンの条件を列挙すると、下記のSQLになります。

ちょっと長ったらしいですね。こういう場合は、A, F パターン以外 と考えた方がシンプルになりますね。

1行で済みましたね。

もっとシンプルな記述はないでしょうか。図のB, C, D, Eのパターンをよくみてください。Date1 は必ず[終了日]以前、Date2 は必ず[開始日]以降、ということに気がついたでしょうか。これを使うと、

Not も使わずに記述できました。

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/279-91b191dd
該当の記事は見つかりませんでした。