ExcelでSQLを使う-013: SQL WHERE節の活用-2 BETWEENとAND
動物のお医者さん 第5章 |
病気になったヒヨちゃんは、ガンもとばさないし、蹴りもしない・・・のでちょっと寂しい。チョビの出生の秘密があかされたり、永遠に研究室の主であろうと誰もが疑わなかった菱川聖子がついに就職口を見つけたり、相変わらず次々と事件は続く。そして、北海道名物の(?)犬ぞりレースの季節がやってきた。謎の男・ブッチャー氏にその才能を認められたハムテルとチョビは、「オレはやるぜ、やるぜ」のリーダー犬・シーザーと共に、果敢にレースに挑むのであったー。(同書の帯より)
◇◇◇◇◇
WHERE 節の高度な活用-2
範囲: BETWEEN と AND
WHERE節では、キーワード BETWEENと ANDを使って特定の範囲の値を検索するように条件を設定することができます。
WHERE <列名> [NOT] BETWEEN <式> AND <式>
SELECT * FROM 売上日 [売上伝票$A1:H100]
WHERE
BETWEEN
GetDate("2018/05/01"), 1
AND
GetDate("2018/05/01"), 99
初日、末日、締日の取得
BETWEENとANDを用いるのは、月次締切り処理などで集計処理範囲を指定する場合などです。その際に、当月の初日と末日、前月の締日と当月の締日などの指定は必須です。それには、GetDate関数を用います。
Public Const 初日 = 1
Public Const 末日 = 99
Public Const 締日 = 20
? GetDate("2018/08/10",初日) ← 2018/08 の初日
2018/08/01
? GetDate("2018/08/10",初日,-1) ← 前月の初日
2018/07/01
? GetDate("2018/08/10",初日,1) ← 来月の初日
2018/09/01
? GetDate("2018/08/10",初日,,1) ← 来年8月の初日
2019/08/01
? GetDate("2018/08/10", 末日) ← 2018/08 の末日
2018/08/31
? GetDate("2018/08/10",末日,-1) ← 前月の末日
2018/07/31
? GetDate("2018/08/10",末日,1) ← 来月の末日
2018/09/30
? GetDate("2018/08/10",末日,,1) ← 来年8月の末日
2019/08/31
? GetDate("2018/08/10", 締日) ← 2018/08 の締日
2018/08/20
? GetDate("2018/08/10",締日,-1) ← 前月の締日
2018/07/20
? GetDate("2018/08/10",締日,1) ← 来月の締日
2018/09/20
? GetDate("2018/08/10",締日,,1) ← 来年8月の締日
2019/08/20
Public Function GetDate(ByVal N As Date, _
Optional Hiduke As Integer = 1, _
Optional MoveM As Integer = 0, _
Optional MoveY As Integer = 0) As Date
Dim IntAjust As Integer
If Hiduke = 99 Then
MoveM = MoveM + 1
Hiduke = 1
IntAjust = -1
End If
GetDate = DateSerial(DatePart("yyyy", N) + MoveY, _
DatePart("m", N) + MoveM, _
Hiduke) + IntAjust
End Function
次は、DSelect関数とGetDate関数を使って2018年5月度の売上伝票を選択しています。
? DSelect("SELECT * FROM [売上伝票$A1:E100]
WHERE 売上日
BETWEEN
#" & GetDate(FormatDateTime("2018/05/01"), 1) & "#
AND
#" & GetDate(FormatDateTime("2018/05/01"), 99) & "#",,Chr(13))
1;3;20190003;2018/05/01;3;山田 太郎
2;4;20190004;2018/05/02;4;木下 三郎
3;5;20190005;2018/05/03;5;津田 淳
4;6;20190006;2018/05/04;6;内田 洋行
? DSelect("SELECT * FROM [売上伝票$A1:E100]
WHERE Str(売上日)
BETWEEN
'" & GetDate(FormatDateTime("2018/05/01"), 1) & "'
AND
'" & GetDate(FormatDateTime("2018/05/01"), 99) & "'",,Chr(13))
1;3;20190003;2018/05/01;3;山田 太郎
2;4;20190004;2018/05/02;4;木下 三郎
3;5;20190005;2018/05/03;5;津田 淳
4;6;20190006;2018/05/04;6;内田 洋行
<売上伝票> |
<売上伝票検索> |
SQLツールユーザーは、以上のようなテストを踏まえて表<売上伝票検索>を作成することになります。
オペレーターが入力するのは、<検索月>のみです。<初日>と<末日>は、式で生成します。
<売上伝票>を検索する式は、C07に書きます。検索した値から<伝票番号><売上日><顧客名>を取り出して表示するには、CutStr関数をネストします。
C07=DSelect("SELECT * FROM [売上伝票$A1:E100]
WHERE Str(売上日)
BETWEEN
'"& TEXT(E6,"yyyy/mm/dd")&"'
AND
'"& TEXT(F6,"yyyy/mm/dd") & "'")
C09=CutSTr(CutSTr(C7, "|",1), ";",1)
D09=CutSTr(CutSTr(C7, "|",1), ";",3)
E09=CutSTr(CutSTr(C7, "|",1), ";",4)
F09=CutSTr(CutSTr(C7, "|",1), ";",6)
VBAでのプログラミングなしでの仕組み。それをセットアップするのは、確かに、少々面倒です。しかし、一度、仕組んでしまえば、<検索月>を入力するだけで表<売上伝票検索>が更新されます。是非、挑戦して貰いたいと思います。
本稿は、ここまでとします。集合、パターン照合については、次稿で紹介します。