AccessVBA値の入替えNullの使い方が不正

Question

以前こちらで教えていただいのですが、
http://oshiete.goo.ne.jp/qa/8086569.html
以下のクエリを実行すると、「Nullの使い方が不正です」とのエラーが出てしまいます。

------------------------------------
テーブル名「T9」
フィールド：顧客番号（テキスト）/ 売上金額（通貨） / 売上月（テキスト
テーブル名「T9A」
フィールド：顧客番号（テキスト）/ 集約先番号（テキスト）

●標準モジュール
Public Function Bango(sSrc As String) As String
　　Dim rs As New ADODB.Recordset
　　Dim sR As String

sR = sSrc
　　rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
　　rs.Close
　　Bango = sR
End Function

●クエリ
SELECT Bango(顧客番号) AS 集約番号, Sum(売上金額) AS 売上金額計, 売上月
FROM T9
GROUP BY Bango(顧客番号), 売上月;
------------------------------------

当初は、テーブル「T9A」には集約先番号を持つ顧客番号のみにするはずでしたが、
便宜上、集約先番号の有無にかかわらず、すべての顧客番号が入ったテーブルを使います。

そのため、「T9A」の集約先番号にはNullがあり、
Nullに対しては、同行の顧客番号を返すように対応したいです。

If IsNull(集約先番号) Then・・・とやるのかな？と浮かびましたが、うまくいかず、困っております。

何卒ご指南くださいますよう、お願いいたいます。

nda23 · Accepted Answer

Q1が少しおかしかったですね。
正しくは以下のようになります。
Q1：
SELECT A.顧客番号,←★これが無かった。
Nz(E.集約先番号,Nz(D.集約先番号,
Nz(C.集約先番号,Nz(B.集約先番号,
A.集約先番号)))) AS 実集約先
FROM (((T9A AS A LEFT JOIN T9A AS B
ON A.集約先番号=B.顧客番号) LEFT JOIN
T9A AS C ON B.集約先番号=C.顧客番号)
LEFT JOIN T9A AS D
ON C.集約先番号=D.顧客番号) LEFT JOIN
T9A AS E ON D.集約先番号=E.顧客番号

それでQ2はこちら
SELECT Nz(B.実集約先,A.顧客番号) AS 集約番号,
売上月,売上金額 FROM T9 AS A LEFT JOIN
Q1 AS B ON A.顧客番号=B.顧客番号←★これが正しい。

Q3はそのままです。

前のクエリだと集約先がない顧客だと上手くいきません。

nda23 · Answer

最大5レベルのネストであれば、以下のようにします。
クエリ:Q1
SELECT Nz(E.集約先番号,Nz(D.集約先番号,
Nz(C.集約先番号,Nz(B.集約先番号,
A.集約先番号)))) AS 実集約先
FROM (((T9A AS A LEFT JOIN T9A AS B
ON A.集約先番号=B.顧客番号) LEFT JOIN
T9A AS C ON B.集約先番号=C.顧客番号)
LEFT JOIN T9A AS D
ON C.集約先番号=D.顧客番号) LEFT JOIN
T9A AS E ON D.集約先番号=E.顧客番号

目的の一つ前のクエリ:Q2
SELECT Nz(B.実集約先,A.顧客番号) AS 集約番号,
売上月,売上金額 FROM T9 AS A LEFT JOIN
Q1 AS B ON A.顧客番号=B.実集約先

目的のクエリ:Q3
SELECT A.集約番号,Sum(A.売上金額) AS 売上金額計,
A.売上月 FROM Q2 GROUP BY A.集約番号,A.売上月

前にも述べましたが、SQLで実現する方法を考えるべきで、
プログラムでやると、700倍以上効率が悪く、特にODBCの
リンクテーブルを使うと、コーヒー一杯飲んでくる時間が
あくほど性能が劣化します。

上記設計で、拙いのはネストが5レベルもあることで、
別のテーブルを置くなりして、1レベルに収まるよう
工夫すべきです。

nda23 · Answer

集約先のネストは何段階まででしょう？
仮に集約先のネストが1レベルしかないと
するならば、以下のクエリで集約先を特定
します。
クエリ：Q1
SELECT A.顧客番号
,Nz(B.集約先,A.集約先) AS 実集約先
FROM T9A AS A
LEFT JOIN T9A AS B
ON A.集約先=B.顧客番号

Nz関数は以下の働きがあります。
Nz(A,B) Aが非NULLならA,AがNULLならB

このクエリの集約先で集計します。
クエリ：Q2
SELECT Nz(B.集約番号,A.顧客番号) AS 集約番号
,SUM(A.売上金額) AS 売上金額計,A.売上月
FROM T9 AS A LEFT JOIN Q1 AS B
ON A.顧客番号=B.実集約先
GROUP BY Nz(B.集約番号,A.顧客番号),A.売上月

サブクエリを使うと、Q1は要らないのですが、
SQLに不慣れのようなので、分けて作る方が
安全でしょう。
こういうものはクエリだけで処理すべきで、
それができない時はテーブルの設計が
間違っています。

30246kiku · Answer

案１）　rs("集約先番号")　が Null でなければ、再度 Bango を呼び出す

> If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
↓
If (Not rs.EOF) Then
　　If (Not IsNull(rs("集約先番号"))) Then sR = Bango(rs("集約先番号"))
End If

案２）　Recordset を得る時の条件に　集約先番号 Is Not Null　を追加してみる

> rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
↓
rs.Source = "SELECT * FROM T9A WHERE 集約先番号 Is Not Null AND 顧客番号='" & sSrc & "';"

案３）　全面的に書き方を変えてみる

Public Function Bango(sSrc As String) As String
　　Dim v As Variant
　　Dim sR As String

v = sSrc
　　While (Not IsNull(v))
　　　　sR = v
　　　　v = DLookup("集約先番号", "T9A", "顧客番号='" & v & "'")
　　Wend
　　Bango = sR
End Function

※ 各処理性能はわかりません
※ 未検証ですので不都合あれば修正してください

※※ テーブル「T9A」での循環には注意してください

ブログに記事として載せてましたので、興味あれば探してみてください。
・自分の管理するURLは記述できない規則です
・また、辿りつくまでのキーワード提示もできません

AccessVBA値の入替えNullの使い方が不正

Q1が少しおかしかったですね。

最大5レベルのネストであれば、以下のようにします。

この回答への補足

集約先のネストは何段階まででしょう？

案１） rs("集約先番号") が Null でなければ、再度 Bango を呼び出す

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

案１）　rs("集約先番号")　が Null でなければ、再度 Bango を呼び出す