クロス集計クエリ内の特定フィールドの有無の確認

質問

クロス集計クエリ内の特定フィールドの有無の確認

いつもお世話になっております。
ご質問です。

【状況】
毎月クラブの部費を集めています。
テーブル「t_buhi_mas」
フィールド（一部抜粋）
・ID_NO（部員の番号）
・ID_NO_s（枝番）
・nm（部員名）
・year_buhi（年）　　2017
・month_buhi（月）　　08
・buhi（部費）　　　1000

クロス集計クエリのデータを基に、別のテーブルにデータを追加します。
クロス集計クエリの結果一例

ID_NO ID_NO_s nm 08 09 10 11
1 1 山田 1000 1000 1000 10000

※08 09 10 11・・・月
この結果を基に別テーブル「tbl_A」にデータを追加したいのですが、以下の場所でエラーとなります。
テーブル「tbl_A」
フィールド（一部抜粋）
・ID_NO（部員の番号）
・ID_NO_s（枝番）
・nm（部員名）
・01（金額を格納）
　～
・12（金額を格納）

Dim h_sql as string
Dim h_year as string

h_year = "2017"
h_sql = "TRANSFORM Sum(buhi) AS buhi_sum " & _
"SELECT ID_NO, ID_NO_s, nm " & _
"FROM t_buhi_mas " & _
"WHERE ([year_buhi] = '" & h_year & "') " & _
"GROUP BY ID_NO, ID_NO_s, nm " & _
"ORDER BY ID_NO, ID_NO_s, month_buhi " & _
"PIVOT month_buhi;"

Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim h_str_fld1 As String

Set rst1 = New ADODB.Recordset
rst1.Open h_sql, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

With rst1

If .RecordCount = 0 Then: GoTo line4:

.MoveFirst
Set rst2 = New ADODB.Recordset
rst2.Open "tbl_A", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

Do Until .EOF

rst2.MoveFirst
Do Until rst2.EOF

If !ID_NO = rst2!ID_NO And !ID_NO_s = rst2!ID_NO_s Then

For i = 1 To 12 '1月～12月の計算

If i < 10 Then
h_str_fld1 = "0" & i
Else
h_str_fld1 = i
End If

On Error Resume Next
If IsNull(rst1(h_str_fld1)) Then 　←ここでエラー
rst2(h_str_fld1) = 0
Else
rst2(h_str_fld1) = rst1(h_str_fld1)
End If
rst2.Update
On Error GoTo 0

Next

GoTo line3:
End If

rst2.MoveNext

Loop
line3:
.MoveNext

Loop

End With

rst2.Close
rst1.close
cnn.close

エラーコメント：このコレクションには項目がありません。
要するに、追加先のテーブル「tbl_A」には毎月の部費を格納するフィールド「01」～「12」があるのですが、
クロス集計結果には「01」がないためだと思います。
On Error Resume Next　で回避できると思っていたのですが作動しません。

クロス集計クエリ内に、「01」など結果に基づいたフィールドが無い場合の回避方法がご存知であれば教えてください。

何卒宜しくお願い申し上げます。

回答No.1

piroin654 · ベストアンサー

> クロス集計クエリ内に、「01」など結果に基づいたフィールドが無い場合の回避方法

は、面倒なので、クロス集計クエリのSQL文の最後のところ、

＞　PIVOT month_buhi;

を

＞　PIVOT Format([month_buhi],'oooo') In ('01','02','03','04','05','06','07','08','09','10','11','12');

のようにして、クロス集計クエリのmonth_buhiフィールドを01から12まで
表示させてみてはどうですか。これなら
If IsNull(rst1(h_str_fld1)) Then
のところでエラーは出ないと思いますが。

お礼

piroin654様

早速のご連絡ありがとうございました。
バッチリうまくいきました。
PIVOTの意味も知らず、ただクロス集計クエリをSQLに変換したものを修正しておりました。
とても勉強になりました。
こらからも宜しくお願い申し上げます。