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

このQ&Aのポイント
  • AccessVBAを使用して値の入れ替えを行う際に、Nullの使い方が不正なエラーが発生しています。
  • テーブル「T9A」には集約先番号を持つ顧客番号のみを設定する予定でしたが、便宜上すべての顧客番号を含むテーブルを使用しています。
  • 集約先番号がNullの場合は、同行の顧客番号を返す処理を行いたいのですが、うまくいっていません。
回答を見る
  • ベストアンサー

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

以前こちらで教えていただいのですが、 http://okwave.jp/qa/q8086569.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
  • ベストアンサー率54% (777/1415)
回答No.4

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はそのままです。 前のクエリだと集約先がない顧客だと上手くいきません。

semi076
質問者

お礼

早速のご回答、ありがとうございました。 試してみたところ、期待したとおりの結果が得られました。 今回はいずれも大変勉強になる回答をいただけたのですが、 最も処理時間の速かった回答者様の投稿をベストアンサーとして選ばせて いただいきました。 どうもありがとうございました。

その他の回答 (3)

  • nda23
  • ベストアンサー率54% (777/1415)
回答No.3

最大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レベルに収まるよう 工夫すべきです。

semi076
質問者

お礼

どうもありがとうございます。 テーブルや効率・・・、 ご指摘の通り工夫が足りませんよね、なんとか改善していきたいです。 動作確認がまだですので、また結果を報告したいと思います。

semi076
質問者

補足

いただいたものをそのままコピペして試してみました。 Sqlだとサクサク動くんですね! しかし、結果が、 Q1クエリの実集約先が、nullのままになってしまいました。 Q3でも集計は顧客番号の集計結果と同じでした。 顧客番号、集約先が文字列だからしょうか? 何度もお手数をおかけしますが、よかったら教えてください。

  • nda23
  • ベストアンサー率54% (777/1415)
回答No.2

集約先のネストは何段階まででしょう? 仮に集約先のネストが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に不慣れのようなので、分けて作る方が 安全でしょう。 こういうものはクエリだけで処理すべきで、 それができない時はテーブルの設計が 間違っています。

semi076
質問者

お礼

ご回答いただきありがとうございます。 情報が漏れており、失礼いたしました。 集約先は最大5段階まで入れ子になっています。 不勉強で申し訳ないのですが、 1段階以上の入れ子構造の場合でも クエリで処理できるものなのでしょうか?

  • 30246kiku
  • ベストアンサー率73% (370/504)
回答No.1

案1) 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 案2) Recordset を得る時の条件に 集約先番号 Is Not Null を追加してみる > rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';" ↓ rs.Source = "SELECT * FROM T9A WHERE 集約先番号 Is Not Null AND 顧客番号='" & sSrc & "';" 案3) 全面的に書き方を変えてみる 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は記述できない規則です ・また、辿りつくまでのキーワード提示もできません

semi076
質問者

お礼

お礼が遅れまして失礼いたしました。 早速のご回答をいただきありがとうございます。 成長せず、何度もお手数をかけてしまい申し訳ないです。 回答者様のブログもあわせて確認しました。 テーブル「T9A」の集約先番号が、Nullが大多数を占めるので、 いただいた案1、案2を、まず試してみたいと思います。 Accessソフトが自宅PCに無いため、 動作確認については明後日以降報告いたします。

semi076
質問者

補足

案1~3まで試して、期待した結果が得られることを確認できました! どうもありがとうございました。 回答者様のブログを参考に、 もっと勉強しようと思います。

関連するQ&A

  • 【Access】特定の顧客のみ集約して集計する

    Access2010で下記のような条件で集計をしたいと思っています。 ------------------------------- テーブル1 フィールド(1):顧客番号 フィールド(2):売上金額 フィールド(3):売上月 001/100円/1月 002/ 50円/1月 003/200円/1月 004/150円/1月 テーブル2 フィールド:(1)顧客番号 フィールド(2)集約先(顧客番号) 001/002 002/003 ★完成クエリ フィールド(1)顧客番号(集約) フィールド(2)売上金額 フィールド(3)売上月 003/350円/1月 004/150円/1月 ------------------------------- 顧客番号ごとの売上金額を集計しますが、 その際、集約先を持つ顧客番号については、 集約先を参照し、顧客番号(集約)という括りで集計したいと思っています。 Excelだと、IF関数を使って「もし集約先セルが空欄なら顧客番号を参照してコピーする」等して、 顧客番号(集約)の列で括って集計する、と考えたりできるのですが、 Accessは不慣れなこともあって、なかなか良い方法が思いつきません・・・。 どうか集計方法をご教授いただけないでしょうか。 よろしくお願いします。

  • AccessVBAでIBM特殊文字を調査

    Access2000でテーブルにある氏名から特殊文字が使用されているか調査したいのです。 テーブルやVBAは下記の通りです。 顧客テーブルがあり、内容はキー番号(半角10バイト)、漢字氏名(全角50バイト)、カナ氏名(半角50バイト)です。 漢字氏名フィールドを調査して特殊文字コードが存在した場合エラーメッセージを表示したいのです。ロジックを以下の通り作成したのですが、すべての漢字氏名がエラーとなってしまいます。 なぜでしょうか?教えていただけますでしょうか。お願いいたします。 Public Sub 調査()   Dim DB As DAO.Database   Dim RS As DAO.Recordset   Dim Moji As String   Set DB = CurrentDb()   Set RS = DB.OpenRecordset("顧客テーブル", dbOpenDynaset) Do Until RS.EOF   If RS!漢字氏名 Like "*[" & Chr("&hFA40") & "-" & Chr("&hFC4B") & "]*"   Then    Msgbox RS!漢字氏名   End If   RS.MoveNext Loop   RS.Close: Set RS = Nothing   DB.Close: Set DB = Nothing End Sub

  • Access VBAで int型にnullを入れる方法について

     Access で作ったテーブルの数値型のフィールド「金額」にnullを入れたいと思い、四苦八苦しています。  以下のソースを書いたのですが、値には「0」が入ってしまい、nullが挿入されません。 Dim a As Long Dim SQL1 As String If IsNull(Me.摘要_会員番号) = True Then IsNull (a) Else a = Me.摘要_会員番号 End If SQL1 = "UPDATE 個人情報入金 SET 個人情報入金.金額 = " & a & ";" DoCmd.RunSQL SQL1  その他、a=null や、a=''をやってみたのですがこれでは入らず、String型ではないので、スペースも入りません。  nullで無くても、目で見てデータが見えない状態であればいいのですが、そのようにupdateをかける方法をしっている方、是非ご教授をお願いします。

  • 副問い合わせでのNULLの抽出方法

    SQLについての質問です。 副問い合わせを使用したとき、NULLのデータも含めて抽出したいの ですが、どうもうまくいきません。 やりたいことを説明すると、まず以下の構造のテーブルがあります テーブル    コード 金額  -------------- 01  |100   01  |110   02  |090   02  |080   03  |200   04  |100   04  |150   05  |400   09  |350   null |100   null |200   集約後データ コード -------- 01  |<--- 1件目 02  |<--- 2件目 03  |<--- 3件目 04  |<--- 4件目 05  |<--- 5件目 09  |<--- 6件目 null |<--- 7件目 まず、テーブルをコードで集約します。集約された結果のデータで上位○件と いう制御をかけたい為、ROWNUMを使用しています。 ただ、実際にほしいデータは集約する前のデータの為、一度集約とROWNUMを使って 絞りこんだデータを副問い合わせしています。 但し、このときNULLが入ったコードも取得する必要があります。 現在のSQLはこんな感じです。 SELECT コード、SUM(金額) FROM テーブル WHERE コード IN ( SELECT コード FROM テーブル WHERE ROWNUM <= 10 GROUP BY コード ) GROUP BY コード このSQLだと、NULLが省かれてしまうため上位6件までしか抽出できません。 実際、NULLを含んだデータを副問い合わせで取得することは可能でしょうか? データベースはオラクル9iです。 宜しくお願いします。

  • 登録番号を詰めたい2

    重要な部分が欠けていたので質問させていただきます。またご教授おねがいします。 以前このような質問をしたものですが、 http://okwave.jp/qa4859369.html 連番がずれてしまっていたので、以前説明できてなかった部分を説明したいと思います。 電話番号や会社名などの個人情報が入っている個人情報テーブル(以下個人T)と 値段や出荷した荷物などの詳細が書かれている注文テーブル(以下注文T)があるとします。 2つのテーブルは"登録番号"とフィールドで関連付けをしてあります。 その登録番号を虫食い無しの綺麗な連番にしたいと思います 以下の様な状態です。 "連番"というのは後の作業で"登録番号"と入れ替えをするために作成されたものです。 注文T    注文T  |  個人T     個人T 登録番号   連番   | 登録番号     連番 ZZZZ0001   null   | ZZZZ0001    ZZZZ0001 ZZZZ0002   null   | ZZZZ0002    ZZZZ0002 ZZZZ0002   null   | ZZZZ0003    ZZZZ0003 ZZZZ0004   null   | ZZZZ0004    ZZZZ0004 ZZZZ0006   null   | ZZZZ0006    ZZZZ0005 (テーブル2には同じ会社から何度も注文がある場合"登録番号"フィールドには同じ"登録番号"が存在する場合がある) ところが、個人Tには個人情報があるにもかかわらず注文Tには注文された内容が無い場合があります。 以前質問した時に教えていただいたものですと Private Sub Sample4()   Dim rs As New ADODB.Recordset   Dim i As Integer   Const フィールド名 As String = "☆連番☆"    ' ☆1   Const テーブル名 As String = "☆元テーブル☆"  ' ☆1   Const 抽出 As String = "☆登録番号☆"      ' ☆1   Const 並び替え As String = "☆登録番号☆"    ' ☆1   Dim sTmp As String   i = 0   sTmp = ""   rs.Source = "SELECT * FROM " & テーブル名 & _         " WHERE " & 抽出 & " LIKE 'ZZZZ%' ORDER BY " & 並び替え & ";"   rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic   While (Not rs.EOF)     If (rs(並び替え) <> sTmp) Then       i = i + 1       sTmp = rs(並び替え)     End If     rs(フィールド名) = "ZZZZ" & Format(i, "0000")     rs.Update     rs.MoveNext   Wend   rs.Close End Sub になりますが、反映させた結果は、 注文T    注文T    | 個人T     個人T 登録番号   連番     | 登録番号    連番 ZZZZ0001   ZZZZ0001   | ZZZZ0001    ZZZZ0001 ZZZZ0002   ZZZZ0002   | ZZZZ0002    ZZZZ0002 ZZZZ0002   ZZZZ0002   | ZZZZ0003☆   ZZZZ0003 ZZZZ0004   ZZZZ0003☆  | ZZZZ0004    ZZZZ0004 ZZZZ0006   ZZZZ0004   | ZZZZ0006    ZZZZ0005 と、いうものになります。 本来個人情報だけがある登録番号(ZZZZ0003)に、登録番号(ZZZZ0004)の情報が加わってしまうのです。 したがってまったく関連がない会社同士の情報が混ざってしまうのです (個人情報しかない"登録番号"でずれてしまう) 教えていただいたものは完璧だったのですが、私が情報の足りない質問をしてしまったのが失敗でした・・・申し訳ありません。 注文T    注文T    | 個人T     個人T 登録番号   連番     | 登録番号    連番 ZZZZ0001   ZZZZ0001   | ZZZZ0001    ZZZZ0001 ZZZZ0002   ZZZZ0002   | ZZZZ0002    ZZZZ0002 ZZZZ0002   ZZZZ0002   | ZZZZ0003    ZZZZ0003 ZZZZ0004   ZZZZ0004   | ZZZZ0004    ZZZZ0004 ZZZZ0006   ZZZZ0005   | ZZZZ0006    ZZZZ0005 注文Tがこのような形になるのが理想なのですが、どのように連番を振る途中で飛ばすのか分かりません。 だいぶVBAが変わってしまいそうな気もしますが、どうかよろしくお願い致します。

  • NULL文字のINSERT

    こんにちは。 SQLServer初心者です。 Access VBAで SQLServerから抽出したデータをDBにINSERT しようとしてるのですが、抽出した項目にNULLデータがあるので エラーになってしまいます。 どのように回避すればよろしいでしょうか? ISNULL関数を使えば回避できるのでしょうか? たとえば以下のようなINSERT分を実行したいのですが、 顧客名_漢字のフィールドにはNULL文字が入ってる場合があるとします。 よろしくお願い致します。 strSQL2 = "" strSQL2 = strSQL2 & " INSERT INTO NORSE_TMP_DATA ( " strSQL2 = strSQL2 & " ポート名" strSQL2 = strSQL2 & ", 顧客名_漢字" strSQL2 = strSQL2 & ", 顧客名_カナ" strSQL2 = strSQL2 & ", 契約状態" strSQL2 = strSQL2 & ", 契約番号" strSQL2 = strSQL2 & ", 契約枝番号" strSQL2 = strSQL2 & ", 顧客番号" strSQL2 = strSQL2 & " VALUES (" strSQL2 = strSQL2 & " '" & rs.Fields("ポート名").Value & "'" strSQL2 = strSQL2 & ", '" & rs.Fields("顧客名_漢字").Value & "'" strSQL2 = strSQL2 & ",'" & rs.Fields("顧客名_カナ").Value & "'" strSQL2 = strSQL2 & ",'" & rs.Fields("契約状態").Value & "'" strSQL2 = strSQL2 & "," & rs.Fields("契約番号").Value strSQL2 = strSQL2 & "," & rs.Fields("契約枝番号").Value strSQL2 = strSQL2 & "," & rs.Fields("顧客番号").Value strSQL2 = strSQL2 & " )"

  • Updateできない・・・

    初心者です。お願いします。 Access2002で人事考課DBを作成中です。 ADOのUpdate処理がうまくいかず困っています。 作業の流れとしては以下のとおりです。 1)職員テーブルを元にしたメニューフォーム(F_人事管理DB)を開く。2)対象となる職員を呼び出す。3)メニューから考課結果入力フォーム(F_情意考課)を開く。 4)考課結果を入力する。5)情意考課テーブル(T_情意考課)に結果が蓄積される。 情意考課テーブル(T_情意考課)と中心の職員テーブル(T_人事考課DB)は職員番号をキーにして繋がり、考課結果入力フォーム(F_情意考課)は情意考課テーブルを元にしています。 考課結果を入力した後「更新後処理」で次のようなプロシージャによって、結果を情意考課テーブルに追加させようと考えました。 Private Sub Form_AfterUpdate() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim bango As String Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset rs.Open "T_情意考課", cn, adOpenKeyset, adLockOptimistic bango = Forms!F_情意考課!テキスト63 rs.Update "職員番号", bango rs.Update "更新日付", Date rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub テキスト63のコントロールボックスには「=Forms!F_人事管理DB!職員番号」という式で職員番号が代入されます。 変数bangoにはデータは代入されているようですが、情意考課テーブルの更新ができずエラーになってしまいます。 どこがおかしいでしょうか? 職員番号は「K000*」という連番で型はテキストです。 この作業中は、F_人事管理DB、F_情意考課のふたつのフォームが開いています。 宜しくお願いします。

  • 'dataType' 引数を Null にすること

    VB.NETで開発をしています。 配列をテーブルに変換するにあたって、新規作成すると、問題なく成功するのですが、 作成中のシステムに組み込みをしますと、Type.GetType("system.stirng")で、「'dataType'引数をNullにすることはできません。」と、エラーが発生します。 原因は、Type.GetType("system.stirng")の結果が、「Nothing」になってしまうからです。 いろいろと試してみましたが、状況が一向に改善されません。 新規作成した方は、「{Name = "String"FullName="System,String"}」と、入っています。 下記を、システムに設置すると、エラーになります。 Dim s1() As String = New String() {"a", "b", "c", "d"} Dim dt As DataTable Dim t_data As Object dt = New DataTable t_data = Type.GetType("system.stirng") dt.Columns.Add("kname", t_data) 以上、宜しくお願い致します。 環境は、Visual Studio 2012です。

  • accessのテーブル二つで一つのテーブルを

    ACCESS97を使用しています。 テーブル1 製品番号  出荷日      出荷数  100     2012/12/1      3  101     2012/12/13      2  105      2012/12/1       5  108      2012/12/5       6 テーブル2 製品番号 売上日 売上数 売上金額 100     2012/12/1   3   3,000 101     2012/12/13   4   5,000 111     2012/12/10   10   500 二つのテーブルがあり、どちらもfrom to の期間指定で大きなテーブルから取り出しています。テーブル1のみにあるデータ、テーブル2にあるデータどちらにもあります。 これを一つのテーブルにまとめて下記のようにしたいのですが、できますか。 製品番号 出荷日   出荷数    売上日     売上数   売上金額 100   2012/12/1     3         2012/12/1      3     3,000 101   2012/12/13     2           2012/12/13       4   5,000 105   2012/12/1      5          null      null     null 108   2012/12/5     6          null      null      null 111     null       null       2012/12/10      10      500 このようなテーブルにしたいのですが、できますか。  access使用歴は長いのですが、簡単なクエリとかしかできなくてなさけなく思っています。これを機に少し勉強したいと思ってますが、取り掛かりからわかりません。よろしくお願いいたします。

  • 変数にnullを代入するには

    諸事情で、変数の値をNullにしたいのですが、 Sub a() Dim mystr As String mystr = "moji" '他の処理 mystr = Null End Sub だと、実行時エラー94になります。 多分、NULLは値じゃないから代入できないんだと思いますが、どうすれば変数をnullにできるのでしょうか? String型が問題であれば、何にすればいいですか?