一、问题描述:
当报表的数据来源是一个交叉表时,由于字段名无法确定,就无法直接生成报表。
二、解决方案:
先生成一个报表模板,对于无法确定的字段标题预定义一个,在报表中对字段不绑定,通过报表的open事件来解决字段绑定。
下面是解决一个报表,最后两列最近的两个月份:
Private Sub Report_Open(cancel As Integer)
Dim db As DAO.Database
Dim q As QueryDef
Set db = CurrentDb
Set q = db.QueryDefs(Me.RecordSource)
Dim i As Integer
Dim thisMonth, preMonth As String
Dim iStatus As Integer
thisMonth = get本月年月字串()
preMonth = get上月年月字串()
iStatus = 0
For i = q.Fields.Count - 1 To 0 Step -1
If q.Fields(i).Name = thisMonth Then
本月.ControlSource = thisMonth
iStatus = iStatus + 1
lb本月充气量.Caption = thisMonth & vbCrLf & "充气量"
Else
If q.Fields(i).Name = preMonth Then
上月.ControlSource = preMonth
iStatus = iStatus + 1
lb上月充气量.Caption = preMonth & vbCrLf & "充气量"
End If
End If
If iStatus >= 2 Then
Exit For
End If
Next i
Set q = Nothing
End Sub
Public Function get本月年月字串()
get本月年月字串 = get日期年月字串(Date)
End Function
Public Function get上月年月字串()
get上月年月字串 = get日期年月字串(GetPreMonthFirstDay())
End Function
Public Function get日期年月字串(d As Date)
get日期年月字串 = CStr(Year(d) * 100 + Month(d))
End Function
Public Function GetPreMonthFirstDay()
GetPreMonthFirstDay = DateSerial(Year(Date), Month(Date) - 1, 1)
End Function