しがないOLのはじめての「Microsoft Access」　～コンボボックスの絞り込み特別編～

こんにちは。クニ吉です。
「STEINS;GATE」の映画がとうとう今週20日公開です。
もちろん初日に行きますとも！
公開されていた予告ムービーで涙した私です・・・。予告で泣かせられるとは・・なんだかくやしい。

さて、今回はコンボボックスの絞り込み特別編です。
急遽思い立って書くことにしました。自分でもはまって調べた箇所なので、お役に立てると幸いです。
多少強引なやり方・・かもしれませんが、一応やりたいことはできました＾＾；

Null値を含めたコンボボックスの抽出

Null値とは？

Null値って何？というところを軽くご説明。
Null値とは、データが認識できない、いわば無効な値であることを表します。
ではどういう状態がNullになるかというと、例えばフィールドに何も値を入力していない状態ではフィールドにはNullが格納されていることになります。
Nullに近いものだと""（ダブルクォーテーション）やEmpty値というものがありますね。
どれも「空白」、「文字が入っていない状態」というイメージで同じもののような気がしてしまいますが、これらはすべて別物です。
”"という値は、文字を1つも含まない「長さ0の文字列」を表しているため、Null値とは異なります。
これらNullや空白文字列の扱いについてはフィールドの「値要求」または「空文字列の許可」で設定することができます。

• 値要求→Nullの許可・不許可
  値要求「はい」→Null値を許可しない
  値要求「いいえ」→Null値を許可する
• 空文字列の許可→長さ0の文字列の許可・不許可
  空文字列の許可「はい」→0の文字列を許可する
  空文字列の許可「いいえ」→0の文字列を許可しない
  

先ほど何も値を入力していない場合はNullが格納されると書きましたが、フィールドの値要求および空文字列の許可を「はい」に設定した場合には、何も入力しなかったフィールドには「長さ0文字列」が格納されることになります。
本記事は、値要求「いいえ」、空文字列の許可「はい」で設定しているため、Nullを格納していることになりますが、見た目は同じ空白でも設定によりフィールドに格納されている値は異なりますので、ご注意下さい。

最後におまけのような感じですが、Empty値はバリアント型変数の初期値のことです。
バリアント型変数には、変数宣言して代入されるまではEmpty値が格納されています。

条件抽出における問題点

Null値が抽出において何が問題かというと、Null値を含んだレコードは正しく抽出されません。
例えばこんな現象が起こります。

状況を確認してみる

考察

これらから何が問題かを考えると・・「空白のフィールド（Null）を探してしまっている」のでないでしょうか
検証1の（2）のように契約開始日と終了日の両方が未入力の場合でも検索は正しく行われません。
検証2で契約開始日と終了日を入力して正しく検索されたことを考えると、おそらく間違いないでしょう。
検証1の（1）の場合は、契約開始日が2/1以降の開始で、かつ終了日が空白になっているレコードを探していることになります。

Null値を含めた検索を行う方法

それを回避するためには、下記のような方法を考えました。

（1）検索用テキストボックスを認識できる値に変換する
　「空白」のままだとNull値のフィールドを検索してしまうため、yyyy/mm/ddの形式に変換します。
　変換する値は、契約開始日の設定であれば創業日でもいいですし、1900/1/1といったような日付でもいいです。
　とにかく何も入力していない場合に全データを検索できるような日付を設定します。

（2）フィールドに格納されているNull値を認識できる値に変換する。
　上記に連動して、こちらも日付の形にしてあげます。
　入力していないフィールドはNull値を格納しており、前述の通りNullは認識できない値です。
　（1）で日付変換しても検索先が日付の形になっていなければ、未入力のフィールドがあるレコードは抽出されません。
　そのため、格納しているフィールドも変換する必要があります。

クエリはフィールドを利用して計算式を作り、テーブルには存在しないフィールドを新たに作成することができます。
下図のように消費税額をクエリ内で計算することもできます。
これはそのクエリの中でのみ生成され、意図的にその値を格納しようとしない限りテーブルの値には影響されません。

この式を使って、値の変換を行いたいと思います。

Null値を認識できる値に変換する方法1：IIF関数とIsNull関数

では、変換する方法ですが、IIF関数とIsNull関数を使ってNull値を認識できる値（日付）に変換します。

IIF関数について

IIF(評価する対象の式,真（True）の場合,偽（False）の場合）
IIF関数は評価する式の結果によって値を返します。
（例）IIF（[得点]>=75,"合格","不合格"）
この場合、[得点]の値が75点以上であれば「合格」を返し、75点未満は「不合格」を返します。
今回で言えば、値がNullの場合は指定した任意の日付を返し、Nullでない場合には格納されている値を返すということになります。
これはExcelのIF関数と同じ考え方ですが、AccessではIF関数は使えません。
AccessではIIFという記述スタイルになります。

IsNull関数について

 IsNull（評価する対象の式）
IsNull関数はNull値が含まれているかどうかを評価し、値を返します。
対象となる式がNullであれば真（True）を返し、Nullでない場合には偽（False）を返します。
（例）IsNull([得点])
この場合、[得点]がNull値かどうかを評価します。

今回問題となっている抽出について、これら2つの関数を組み合わせて式を作ると下記のようになります。

空白の場合は指定した日付をセットしますので、日付が空欄でも抽出してくれるようになります。

Null値を認識できる値に変換する方法2：Nz関数

実はもうひとつ変換する方法があります。こっちのほうが簡単です。
IIF関数とIsNull関数についてはあえて書きましたが、今回の処理についてはNz関数のほうが適しています。

Nz関数について

Nz(評価する対象の式,Null値の場合）
評価する対象の式（第1引数）がNull値の場合、第2引数で指定した値を返します。Null値でない場合は第1引数の値をそのまま返します。
（例）Nz([得点],0）
この場合、[得点]がNull値であれば第2引数で指定した「0」を返し、Null値でなければ[得点]の値を返します。

IIF関数とNz関数の違い

IIF関数はExcelのIF関数同様、真偽の結果でそれぞれ指定した値を返すのに対し、Nz関数はNull値でない時は評価対象に格納した値を返します。
今回のように評価対象となる式の値をそのまま使う場合には、Nz関数を使用するほうが簡単です。
前述のIIF関数とIsNull関数を組み合わせた式（図参照）では、[ContractStart]や[Forms]![T_Quotation]![txtContractStart]などがひとつの式で重複しています。
しかし、[得点]から「合格」,「不合格」といった評価対象となる式から全く異なる結果を返す場合は、IIF関数を利用する必要があります。
場合によってはIIF関数とNz関数の2つを利用することもありますし、どういった関数があるのかを理解しておくと幅が広がります。

とりあえずこれらの式により、空白のフィールドを含むレコードも抽出できるようになりました。
もしかしたら変則的なやり方なのかもしれませんが、やりたいことは出来たのでよしとします！

とりあえず今回はこのへんで～