AccessVBA＠レポートフッタへの項目追加

質問

AccessVBAで取引先別の売上金額を出力するレポートを修正しているのですが、
どうもAccessのレポートの取扱いは難しくて苦戦しています。

取引先名 金額　レポートヘッダ
----------------
取引先A 1000
取引先A 2000
取引先A 3000
計 6000
取引先B 1000
取引先B 1000
計 2000
取引先C 1000　→明細
計 1000　→グループフッタ
総合計 9000　→レポートフッタ

というような構成で、動作しているものです。

これに対して、取引先の中の一部のみの合計を出して欲しいという要求があったため、
レポートフッタの最後に　sum_kというフィールドを追加し、以下のようにソースを修正しました。

レポートを開く時に値を初期化します。

Private Sub Report_Open(Cancel As Integer)
Me!kingaku.ControlSource=xxxxxxxx
（中略）
Me!sum_k.Value = 0 '追加
End Sub

ただし、kingakuは明細行のレコードソース上の項目を動的にReport_Openの中で変更しています。
（月別合計金額を列に持っているテーブルが対象なので、月によって列を変更する必要があるため）

Private Sub グループフッター0_Print(Cancel As Integer, PrintCount As Integer)
（中略）　以下を追加
Select Case 取引先コード
Case "A"
Case "C"
Me!sum_k = Me!sum_k + kingaku
Case Else
End Select
End Sub

上記のように修正しましたが、結果はなぜかsum_kの部分がブランクのままです。
ちなみに追加した項目sum_kのControlSourceはブランクにしてあります。

Me!sum_kをカウンタに使わず、プロシジャ上の変数に加算すれば正常に計算はされているようなので、レポートフッターのPrintイベントやFormatイベントの中で、変数の値をレポートヘッダのイベントで追加した項目sum_kに送ってみましたが、うまくいきません。

他に対処方法がありましたら、アドバイス頂けると幸いです。

回答No.9

山田 太郎（@f_a_007） · ベストアンサー

【再回答】確認１、２、３がOKだとして。

*******************
　やり方１
*******************

Private Sub グループフッター1_Format(Cancel As Integer, FormatCount As Integer)
　　Select Case Me.取引先
　　　　Case "取引先A"
　　　　　　Me.取引先A合計 = Me.取引先別取引額合計
　　　　Case "取引先B"
　　　　　　Me.取引先B合計 = Me.取引先別取引額合計
　　　　Case "取引先C"
　　　　　　Me.取引先C合計 = Me.取引先別取引額合計
　　　　Case Else
　　End Select
End Sub

[グループフッター0_Format]でなく[グループフッター1_Format]に記述する。

理由：[グループフッター0_Format]では最後の取引先しかヒットしない。

*******************
　やり方2
*******************

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
　　Select Case Me.取引先
　　　　Case "取引先A"
　　　　　　Me.取引先A合計 = Nz(Me.取引先A合計) + Me.取引額
　　　　Case "取引先B"
　　　　　　Me.取引先B合計 = Nz(Me.取引先B合計) + Me.取引額
　　　　Case "取引先C"
　　　　　　Me.取引先C合計 = Nz(Me.取引先C合計) + Me.取引額
　　　　Case Else
　　End Select
End Sub

詳細フォーマットでレポートフッターの合計欄をFormatの度に加算する。

PS、お詫び！

Accessに触ったのが1996年が最後。相当に、ピント外れの初期回答でした。お詫びしておきます。

お礼

f_a_007 さん　何度もコメントを頂きましてありがとうございました。
おかげさまで無事解決できました。

私も同じように20世紀のころに少しだけかじったAccessで、人の作ったソースの修正なので、勝手が判らずに困っておりました。

結局のところ、ご指摘のソースでの、
Me.取引先C合計 = Nz(Me.取引先C合計) + Me.取引額
のNz関数が足りなかっただけのことが判明しました。

Aceessで使うVBAは手続型言語でありながら、独特の癖があって難しいですね。

コメントが遅れまして失礼しました。

回答No.10

山田 太郎（@f_a_007）

【補足】マジックナンバー追放問題について。

Private Sub グループフッター1_Format(Cancel As Integer, FormatCount As Integer)
　　Select Case Me.取引先
　　　　Case "取引先A"
　　　　　　Me.取引先A合計 = Me.取引先別取引額合計
　　　　Case "取引先B"
　　　　　　Me.取引先B合計 = Me.取引先別取引額合計
　　　　Case "取引先C"
　　　　　　Me.取引先C合計 = Me.取引先別取引額合計
　　　　Case Else
　　End Select
End Sub

＞プログラムコード中に"取引先A"などを埋め込むと
＞プログラマはその後にコードの改変などの作業に追われる。
＞よって、いわゆるマジックナンバーを埋め込むのは避けるべき。

このことは承知の上で、先のコードを提示しています。

【マジックナンバー追放にはコストがかかる】

１、Report.OpenArgs の利用はユーザーに歓迎されない。

OpenArgsプロパティを利用して"取引先名"等を指示できるが、これはユーザーに＜指示や選択する＞という面倒を強いることになり。

２、[得意先マスター].[フッター合計表示]（Yes/No)列。

こういう仕掛けをすれば、ユーザーの負担は皆無。そして、同列の管理アプリケーションのレポートのフッター欄を最適化して保存すれば万全である。しかし、これには３、４日というプログラミング作業が必要。

３、マジックナンバーの人力修正が一番。

今回の案件は、２の作業をして達成すべきだろうか？そこまでシビアに考えなくて、チャッチャと毎月人力で修正しても良いのでは。

ちょっと、動的に対応の私見を補足しておきます。

お礼

コメント有難うございます。

こういうソースへのコード埋め込みは確かに悩ましい問題ですね。
本来ならば、データの中に含まれる別コードをWhere条件にするなり、キーにするなりして対応するのがスマートなやり方でしょうね。

しかし、仰る通り、掛けられる工数と修正頻度のバランスを考えて今回はソース埋め込みにしています。
鶴の一声には勝てませんので、早く解決するしかないですしね。

回答No.8

山田 太郎（@f_a_007）

【確認３】

グループ化は次のようでしょうか？

PS、これですと、先のやり方では＜最後の取引先＞のみ反映されます。

回答No.7

山田 太郎（@f_a_007）

【確認２】

レポートの構成は以下のようでしょうか？

回答No.6

山田 太郎（@f_a_007）

どーも、話が合わないと思って・・・。
よくよく、質問文を読んだら・・・。
もしかしたら、添付図のようなレポートでしょうか？
でしたら、先の回答では目的は達成できません。

[確認]新しい添付図では、ちゃんと合計していると思います。

先ずは、レポートの構成を確認したいと思います。

回答No.5

m3_maki

自分が作るとしたら

sum_k の コントロールソース を 有り得ないフィールド名で設定しておきます。
=Sum(IIf([取引先コード] In ('A','C'),[○○],0))

レポートオープン時に実際のフィールド名に置き換えます。
Private Sub Report_Open(Cancel As Integer)
　　Me!kingaku.ControlSource=xxxxxxxx
　（中略）
　　Me!sum_k.ControlSource= Replace(Me!sum_k.ControlSource, "○○", xxxxxxxx )
End Sub

これで、他の集計処理は不要になります。

もし、集計する取引先が変動する可能性が大だったり
取引先数が多かったりするなら
テーブル化 して In 句 で サブクエリを使うのもお勧め。
あるいは、レポートのレコードソースにフラグを含めてしまうのもありでしょう。

回答No.4

山田 太郎（@f_a_007）

回答１について

・取引先フッターを右クリックして[イベントビルド・・・]。
・後は、先のコードを書くだけ。

既に、取引先フッターの[取引先別取引合計]は既に集計されていますので、これでOKな筈だと思いますよ。

お礼

有難うございます。

私の理解不足かもしれませんが、グループフッタで該当する取引先の行は複数回にわたって発生しますので、加算していかないと、最後の値になってしまうと思います。

回答No.3

山田 太郎（@f_a_007）

添付図が間違っていました！

回答No.2

山田 太郎（@f_a_007）

【別解】ユーザー関数でダイレクトに集計。

添付図のようにユーザー関数でダイレクトに集計する手もあります。

SELECT SUM(XXX) FROM YYYYY WHERE ・・・

仮に、このSELECT文のYYYYYにレポートのレコードソースを指定出来たらダイレクトに集計することも可能。以下は、そういう考えを具体化したものです。

=DBLookup("SELECT SUM(取引額) FROM (" & Replace([RecordSource],";","") & ") WHERE 取引先='取引先A'")

結果は、先の添付図に一致します。

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

お礼

再度のコメントありがとうございました。

そうですね。SQLで書くという手がありますね。

ただ、クエリ自体がパラメタクエリーで、その結果に出来上がった列を動的に選択して、その列を合計という処理になりますので、パラメタクエリの実行とその結果のクエリというSQLになるので、ちょっと難解で、AccessのSQLがそこまでサポートできるのか判りませんが、その方向で進めてみるしかなさそうですね。
思ったより、大変な処理のようです。

回答No.1

山田 太郎（@f_a_007）

Accessを触ったのは1996年が最後。だから、もしかしたら外しているかも・・・。

Option Compare Database
Option Explicit

Private Sub グループフッター0_Format(Cancel As Integer, FormatCount As Integer)
　　Select Case Me.取引先
　　　　Case "取引先A"
　　　　　　Me.取引先A合計 = Me.取引先別取引額合計
　　　　Case "取引先B"
　　　　　　Me.取引先B合計 = Me.取引先別取引額合計
　　　　Case "取引先C"
　　　　　　Me.取引先C合計 = Me.取引先別取引額合計
　　　　Case Else
　　End Select
End Sub

これで、一応は目的を達しているようです。
（添付図を参照されたし）

お礼

早々のコメント有難うございます。

Me.取引先A合計 = Me.取引先別取引額合計
ですと、加算されず、値が置き換わってしまいます。
Me.取引先A合計 = Me.取引先A合計 + Me.取引先別取引額合計
の必要があると思います。

不思議なのは、ブレイクポイントに設定して値をデバッグ表示してみると、
上記例でいう、Me.取引先別取引額合計には値が入っていますが、
演算後のMe.取引先A合計にはNullが入っており、演算できてないのです。