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

  • 2008/09/06 (土) PM 22:10
今日は、中の人の備忘録だから、いつも見に来てる方はスルーしてね☆

<やりたいこと>
  • Accessのデータでフィールド同士を計算する(支店A+支店B+支店Cの日々の合計を計算)
  • Access(MDBファイル)のデータ(上記の内容)をExcel側で操作する。

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




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

売上げ少なっ!!
リアルだったら倒産だなw




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




なんじゃ、コリャ~!Σ( ̄□ ̄|||)

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




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

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

<例1 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を利用する方法は、多くのサイトで紹介されてるので、普通に検索すれば見つかります。

なんか、無駄に疲れた

ホビー/グッズ販売 通販ショップの駿河屋 ブックオフオンライン【PC・携帯共通】

Comments:0

Comment Form




Remember personal info


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

検索
Search
カスタム検索
Amazon.co.jpアソシエイト
フィギュア/ドール/衣装
figure/Doll


POPメイト「ミュウ ビキニバージョン」

東京ドール(ビキニ)ホビーサーチ
※レビューはコチラ



























モバイルサイト
for Mobile

http://taitan-no.net/m/


※PCではアクセス出来ません。

週間アクセスランキング
Weekly Access Ranking
アクセスランキング

Page Top