|
▼さつき さん:
>Cells(n, 1) = rst.Fields("品名").Value
>の=以降のところを = hinban
>などとすると、こんどは値は正しいのですが、「null値の使い方が不正です」と出ます。
↑はString 型の変数にNullを格納しようとした為起こったものです。
型定義をしなければこのエラーは出ないと思いますよ。
SQL変更しないでマクロで処理してみました。
Type TANTOUCHK
Tantou1 As Variant
Hinmoku() As Variant
End Type
Type Zokusei
Tenmei As Variant
Tantou() As TANTOUCHK
End Type
Sub TEST3()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Tenpo() As Zokusei
ReDim Tenpo(0)
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\999393\My Documents\週報.mdb"
cnn.Open
Set rst = New Recordset
rst.Open "累積", cnn
rst.MoveFirst
Do Until rst.EOF
Chk = False
BufCnt = UBound(Tenpo)
For I = 0 To BufCnt
If Tenpo(I).Tenmei = rst.Fields("店名") Then
IntBuf = I
Chk = True
End If
Next
If BufCnt = 0 And Tenpo(0).Tenmei = "" Then
BufCnt = 0
Else
BufCnt = BufCnt + 1
End If
If Not Chk Then
ReDim Preserve Tenpo(BufCnt)
Tenpo(BufCnt).Tenmei = rst.Fields("店名")
ReDim Tenpo(BufCnt).Tantou(0)
IntBuf = BufCnt
End If
Chk = False
BufCnt = UBound(Tenpo(IntBuf).Tantou)
For I = 0 To BufCnt
If Tenpo(IntBuf).Tantou(I).Tantou1 = rst.Fields("契約者名") Then
IntBuf2 = I
Chk = True
End If
Next
If BufCnt = 0 And Tenpo(IntBuf).Tantou(0).Tantou1 = "" Then
BufCnt = 0
Else
BufCnt = BufCnt + 1
End If
If Not Chk Then
ReDim Preserve Tenpo(IntBuf).Tantou(BufCnt)
Tenpo(IntBuf).Tantou(BufCnt).Tantou1 = rst.Fields("契約者名")
ReDim Tenpo(IntBuf).Tantou(BufCnt).Hinmoku(0)
IntBuf2 = BufCnt
End If
Bg = UBound(Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku)
If Bg = 0 And Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(0) = "" Then
Bg = 0
Else
Bg = Bg + 1
End If
ReDim Preserve Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(Bg)
Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(Bg) = rst.Fields("品名")
rst.MoveNext
Loop
RowInd = 1
For I = 0 To UBound(Tenpo)
CtALL = 0
Cells(RowInd, 1) = Tenpo(I).Tenmei
For J = 0 To UBound(Tenpo(I).Tantou)
Cells(RowInd, 2) = Tenpo(I).Tantou(J).Tantou1
Ct = UBound(Tenpo(I).Tantou(J).Hinmoku)
CtALL = CtALL + Ct + 1
For K = 0 To Ct
Cells(RowInd, 3 + K) = Tenpo(I).Tantou(J).Hinmoku(K)
Next
RowInd = RowInd + 1
Cells(RowInd, 2) = "<件数>"
Cells(RowInd, 3) = Ct + 1
RowInd = RowInd + 1
Next
Cells(RowInd, 2) = "計"
Cells(RowInd, 3) = CtALL
RowInd = RowInd + 1
Next
End Sub
|
|