AccessのNz関数を使わないで、フィールド同士を計算する方法

今日は、中の人の備忘録だから、いつも見に来てる方はスルーしてね☆

＜やりたいこと＞

• Accessのデータでフィールド同士を計算する（支店Ａ＋支店Ｂ＋支店Ｃの日々の合計を計算）
• Access（MDBファイル）のデータ（上記の内容）をExcel側で操作する。

とりあえず、AccessのMDBファイルに「てーぶる」というテーブルがあると仮定します。（紛らわしいな）
その中には、「売上げ日」「支店Ａ」「支店Ｂ」「支店Ｃ」というフィールドが定義されてます。
テーブルデザインは、下図の通り。

サンプルとして「てーぶる」のデータは、下図の様になっているものとします。

売上げ少なっ！！

リアルだったら倒産だなw

日々の各支店の合計を知りたいので、「支店Ａ」「支店Ｂ」「支店Ｃ」のフィールドを連結演算子で足し算します。
とりあえず、選択クエリで下図の様に入力してみると・・・

なんじゃ、コリャ～！Σ(￣□￣|||)

全支店の売上げが発生した「20080707」のみ合計されてるが、それ以外はNull値がある為に、合計もNullになってしまう。
ちなみに、Nullになっている箇所に対して0を入力しておけば合計がちゃんと出ます。
Accessのヘルプによると、「Null値が一つでも含んでいると、式全体の評価がNullになる」とのこと。（仕様）

こういう時は、あれだ。Nullをゼロにすればいいのよ

Null値を含むフィールドを計算する場合は、Nz関数を使用し、Null値を0として計算することが出来ます。
※余談だけど、Nz関数は、数値以外に文字列でも出来るよ。

＜例１　Null値を0とする＞
Nz(フィールド名,0)

先ほどのクエリでNz関数を追加してみた。




結果は、この通り。ちゃんとNullの所も計算出来てます。
※合計フィールドのデータ型が通貨型になっていませんが、合計の式でCcur関数を使えば通貨型になります。

Accessで、フィールド同士の計算は、コレで大丈夫だね！

しかーし！ADOでNz関数を使うと、エラーになるよ。

なっ、なんだってぇ～！！！！（MMR風）

例として、Excecl（VBA）でADOを利用し、Accessのデータを取り込むorクエリ実行する際は、下図のエラーが発生。

実行エラー '-2147217900 (80040e14)';



VBSでADOを利用しても同様にエラー



Excel（VBA）でADOを利用し、下記のSQLを実行してもエラー
SELECT 売上げ日,支店A,支店B,支店C, Nz(支店A,0)+Nz(支店B,0)+Nz(支店C,0) AS 合計 FROM てーぶる;

以上の結果からADOを利用して、Nz関数含んだクエリを実行したり、Nz関数を含んだSQLを発行すると必ずエラーが発生します。
ADOはNz関数をサポートしていないのが原因なのかな？

じゃ、どうしたらいいのさ？

IIF関数とIsNull関数を利用したら、Nz関数みたいな事がでけた。

まずは、IIF関数でNull値かどうか条件分岐する。
・Null値なら、 IsNull関数でゼロにする。
・Null値でなければ、任意のフィールドのデータを表示する。

これで、Null値を適切に処理ができます。Nz関数を使う場合に比べ、式が少し長くなるのが難点ですね(^^;)
あとは、下記の様に書き直せばNz関数と同じ様な処理を再現出来ます。

結果として、Nz関数を使った場合と同じ値になるよ

データ型を指定しなくても、参照元のフィールドと同じデータ型になってくれるのが良いなw

で、Excel（VBA）及びVBSでADOを使ってクエリ出来なかったのをもう一度、実行してみるとちゃんと出来るようになりました。
Excel（VBA）からSQLを発行しても問題ありません。
SELECT 売上げ日,支店A,支店B,支店C, IIf(IsNull(支店A),0,支店A)+IIf(IsNull(支店B),0,支店B)+IIf(IsNull(支店C),0,支店C) AS 合計 FROM てーぶる;

これで、ExcelからADOを使ってデータを抽出出来ます。
しかも合計は、ADOで処理出来るので、抽出したデータをExcel側でSUMし直さなくて良いから楽かな。

あと、合計で「\0」を空欄にしたい場合は、以下のSQLでも行けると思います。
SELECT 売上げ日,支店A,支店B,支店C, IIf(IIf(IsNull(支店A),0,支店A)+IIf(IsNull(支店B),0,支店B)+IIf(IsNull(支店C),0,支店C)=0,Null,IIf(IsNull(支店A),0,支店A)+IIf(IsNull(支店B),0,支店B)+IIf(IsNull(支店C),0,支店C)) AS 合計 FROM てーぶる;

ちなみに、ExcelからADOを利用する方法は、多くのサイトで紹介されてるので、普通に検索すれば見つかります。

なんか、無駄に疲れた