代码如下﹐报表是能过代码去创建的,没有使用数据环境。 在我的计算机上运行没有安装运行都没有问题,但系别的计算机上安装运行,这个报表就打不开了。 Option ExplicitDim adoCn As New ADODB.Connection Dim adoCm As New ADODB.Command Dim adoRs As New ADODB.Recordset Dim sumRs As New ADODB.Recordset Dim querySQL As String Dim sumSQL As String Dim fieldCount As Integer Private Sub DataReport_Initialize()On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False"
querySQL = "SELECT d.*,加工资 FROM (Select sd.workid,wi.name,left(dp.fullname,3) as dpname,wd.salary,workday,overflowday,ot,restot,holidayot,holidayrestday,worksalary,overflowsalary,otsalary,restotsalary,holidayotsalary,holidayrestsalary,format(worksalary+overflowsalary+otsalary+restotsalary+holidayotsalary+holidayrestsalary,'#####0.00') as totalsalary " & _ "From salary_detail sd, salary_source ss, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi, param_dp dp Where sd.WorkID = ss.WorkID And sd.WorkID = wd.WorkID And sd.WorkID = wi.WorkID And wd.DpID = dp.DpID And sd.begindate = ss.begindate and year(sd.begindate)=" & Year(Rpt_Date) & " and month(sd.begindate)=" & Month(Rpt_Date) & " and sd.workid like '%" & Rpt_WorkID & "%' and wd.dpid like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & " order by sd.workid) D " & _ "Left Join (select workid,加工资 from worker_changeitem where salaryyear=" & Year(Rpt_Date) & " and salarymonth=" & Month(Rpt_Date) & ") C On d.workid=c.workid"
adoRs.Open querySQL, adoCn Set rpt_TotalSalaryDetail.DataSource = adoRs
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/ Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/ Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/ Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/
Dim Ctl As Object
' Section1 --- 细部 For Each Ctl In rpt_TotalSalaryDetail.Sections.Item("Section1").Controls
'Case "RptImage" ' DSec1_RptImg_Collection.Add Ctl '/细部控件 RptImage/ End Select Ctl.Left = 0 Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./ Ctl.Height = 250 Ctl.Width = 650 Ctl.Visible = False Next Ctl '以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。 Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/ Set bFmt = New StdDataFormat bFmt.Type = fmtBoolean bFmt.TrueValue = "是" bFmt.FalseValue = "否" Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定 With DSec1_RptShp_Collection.Item(i + 1) .Visible = True
If i < ColCount Then If i = 0 Then .Left = 0 Else .Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width End If .Top = 0
Else If i < ColCount * 2 Then If i = ColCount Then .Left = 0 Else .Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width End If .Top = 0 Else If i = ColCount * 2 Then .Left = 0 Else .Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width End If .Top = 0 End If End If
.Height = 250 .Width = 650 .BorderColor = vbWhite .BorderStyle = rptBSSolid .Shape = rptShpRectangle End With ' 细部资料设定 With DSec1_RptTxt_Collection.Item(i + 1) .Visible = True .Height = DSec1_RptShp_Collection.Item(i + 1).Height .Left = DSec1_RptShp_Collection.Item(i + 1).Left .Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30 .Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/ .Font.Name = "" .Font.Size = 8 .Font.Bold = False .Font.Italic = False .Font.Strikethrough = False .Font.Underline = False .ForeColor = vbBlue .DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/ '/可根据字段数据类型设置资料格式/ Select Case adoRs.Fields.Item(i).Type Case adBigInt, adInteger, adSmallInt '.DataFormat.Format = "###,##0" '/数字/ '.Alignment = rptJustifyRight .Alignment = rptJustifyCenter Case adBoolean Set .DataFormat = bFmt '/布尔型字段设定自定义格式/ .Alignment = rptJustifyCenter Case adSingle, adDouble .DataFormat.Format = "#####0.00" .Alignment = rptJustifyCenter Case adCurrency '.DataFormat.Format = "###,##0.00" '/货币/ .DataFormat.Format = "#####0.00" .Alignment = rptJustifyCenter Case adDate, adDBDate, adDBTimeStamp .DataFormat.Format = "Long Date" '/日期、时间/ .Alignment = rptJustifyRight Case Else '/其它,如:文本等/ '.Alignment = rptJustifyLeft .Alignment = rptJustifyCenter End Select End With Next i
sumSQL = "SELECT format(sum(workday),'#####0.00') as sWD,format(sum(overflowday),'#####0.00') as sOFD,format(sum(ot),'#####0.00') as sOT,format(sum(restot),'#####0.00') as sROT,format(sum(holidayot),'#####0.00') as sHOT,format(sum(holidayrestday),'#####0.00') as sHRD,format(sum(worksalary),'#####0.00') as sWS,format(sum(overflowsalary),'#####0.00') as sOFS,format(sum(otsalary),'#####0.00') as sOTS,format(sum(restotsalary),'#####0.00') as sROTS,format(sum(holidayotsalary),'#####0.00') as sHOTS,format(sum(holidayrestsalary),'#####0.00') as sHRS " & _ "FROM salary_source ss, salary_detail sd, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi " & _ "WHERE sd.workid=ss.workid and sd.workid=wd.workid and sd.workid=wi.workid and sd.begindate=ss.begindate and year(sd.begindate)=" & Year(Rpt_Date) & " and month(sd.begindate)=" & Month(Rpt_Date) & " and sd.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable
sumRs.Open sumSQL, adoCn If Not (sumRs.BOF And sumRs.EOF) Then With rpt_TotalSalaryDetail .Sections(5).Controls("Label22").Caption = sumRs("sWD") .Sections(5).Controls("Label23").Caption = sumRs("sOFD") .Sections(5).Controls("Label24").Caption = sumRs("sOT") .Sections(5).Controls("Label25").Caption = sumRs("sROT") .Sections(5).Controls("Label26").Caption = sumRs("sHOT") .Sections(5).Controls("Label27").Caption = sumRs("sHRD") .Sections(5).Controls("Label28").Caption = sumRs("sWS") .Sections(5).Controls("Label29").Caption = sumRs("sOFS") .Sections(5).Controls("Label30").Caption = sumRs("sOTS") .Sections(5).Controls("Label31").Caption = sumRs("sROTS") .Sections(5).Controls("Label32").Caption = sumRs("sHOTS") .Sections(5).Controls("Label33").Caption = sumRs("sHRS") End With End If
Exit Sub ErrHandler: 'MsgBox "错误:" & Err.Number & vbCrLf & "错误信息:" & Err.DescriptionEnd Sub Private Sub DataReport_QueryClose(Cancel As Integer, CloseMode As Integer) Rpt_Date = Empty Rpt_WorkID = Empty Rpt_DPID = Empty Rpt_FromDate = Empty Rpt_ToDate = Empty Rpt_Enable = Empty
If adoRs.State = adStateOpen Then adoRs.Close If sumRs.State = adStateOpen Then sumRs.Close If adoCn.State = adStateOpen Then adoCn.Close Set adoRs = Nothing Set sumRs = Nothing Set adoCn = Nothing
End Sub
不好意思,以上贴错了,是以下代码: *******************************Option Explicit Dim adoCn As New ADODB.Connection Dim adoCm As New ADODB.Command Dim adoRs As New ADODB.Recordset Dim sumRs As New ADODB.Recordset Dim querySQL As String Dim sumSQL As String Dim fieldCount As Integer Private Sub DataReport_Initialize()
'以下根据控件所在区域(Sections)和所属控件类别等将它们分成若干集合
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/ Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/ Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/ Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/ Dim Ctl As Object
'* 注意: ' 报表所显示字段必须与RecordSet所返回的记录一致(报表字段<=返回记录字段), 如果报表显示字段大于返回记录字, 则会引发以下错误: ' Run-time error '3265' ' Item cannot bo found in the collection corresponding to the requested name or ordinal(序数). On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False" querySQL = "SELECT sm.workid,name,dpname,salary,totalsalary,tax,管理费,社保费,厂证,加项,减项,罚项,paysalary " & _ "FROM (select sf.*,wi.name,left(dp.fullname,3) as dpname,wd.salary from (select t.*,管理费,社保费,伙食费 from salary_total t Left join worker_fixitem f on t.workid=f.workid where t.salaryyear=" & Year(Rpt_Date) & " and t.salarymonth=" & Month(Rpt_Date) & " order by t.workid) sf,worker_info wi,(select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,param_dp dp where sf.WorkID=wi.WorkID and sf.WorkID=wd.WorkID and sf.workid like '%" & Rpt_WorkID & "%' and wd.DpID=dp.DpID and wd.DpID Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") sm " & _ "LEFT JOIN worker_changeitem wc ON sm.workid=wc.workid" adoRs.Open querySQL, adoCn Set rpt_PaySalaryDetail.DataSource = adoRs ' Section1 --- 细部 For Each Ctl In rpt_PaySalaryDetail.Sections.Item("Section1").Controls Select Case TypeName(Ctl) Case "RptShape" DSec1_RptShp_Collection.Add Ctl '/细部控件 RptShape/
Case "RptTextBox" Ctl.DataField = adoRs.Fields.Item(0).Name '/先将所有TextBox(RptTextBox) 控件绑定到某一字段, 否则报错!/ DSec1_RptTxt_Collection.Add Ctl '/细部控件 RptTextBox/ End Select Ctl.Left = 0 Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./ Ctl.Height = 250 Ctl.Width = 800 Ctl.Visible = False Next Ctl '以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。 Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/ Set bFmt = New StdDataFormat bFmt.Type = fmtBoolean bFmt.TrueValue = "是" bFmt.FalseValue = "否" Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定 With DSec1_RptShp_Collection.Item(i + 1) .Visible = True
If i = 0 Then .Left = 0 Else .Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width End If .Top = 0
.Height = 250 .Width = 800 .BorderColor = vbWhite .BorderStyle = rptBSSolid .Shape = rptShpRectangle End With ' 细部资料设定 With DSec1_RptTxt_Collection.Item(i + 1) .Visible = True .Height = DSec1_RptShp_Collection.Item(i + 1).Height .Left = DSec1_RptShp_Collection.Item(i + 1).Left .Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30 .Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/ .Font.Name = "" .Font.Size = 8 .Font.Bold = False .Font.Italic = False .Font.Strikethrough = False .Font.Underline = False .ForeColor = vbBlue .DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/ '/可根据字段数据类型设置资料格式/ Select Case adoRs.Fields.Item(i).Type Case adBigInt, adInteger, adSmallInt '/数字/ .Alignment = rptJustifyCenter Case adBoolean '/布尔型字段设定自定义格式/ Set .DataFormat = bFmt .Alignment = rptJustifyCenter Case adSingle, adDouble '/小数/ .DataFormat.Format = "#####0.00" .Alignment = rptJustifyCenter Case adCurrency '/货币/ .DataFormat.Format = "#####0.00" '"###,##0.00" .Alignment = rptJustifyCenter Case adDate, adDBDate, adDBTimeStamp .DataFormat.Format = "Long Date" '/日期、时间/ .Alignment = rptJustifyRight Case Else '/其它,如:文本等/ .Alignment = rptJustifyCenter 'rptJustifyLeft End Select End With Next i
不好意思,以上贴错了,是以下代码: ******************************* Option Explicit Dim adoCn As New ADODB.Connection Dim adoCm As New ADODB.Command Dim adoRs As New ADODB.Recordset Dim sumRs As New ADODB.Recordset Dim querySQL As String Dim sumSQL As String Dim fieldCount As Integer Private Sub DataReport_Initialize()
'以下根据控件所在区域(Sections)和所属控件类别等将它们分成若干集合
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/ Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/ Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/ Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/ Dim Ctl As Object
'* 注意: ' 报表所显示字段必须与RecordSet所返回的记录一致(报表字段<=返回记录字段), 如果报表显示字段大于返回记录字, 则会引发以下错误: ' Run-time error '3265' ' Item cannot bo found in the collection corresponding to the requested name or ordinal(序数). On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False" querySQL = "SELECT sm.workid,name,dpname,salary,totalsalary,tax,管理费,社保费,厂证,加项,减项,罚项,paysalary " & _ "FROM (select sf.*,wi.name,left(dp.fullname,3) as dpname,wd.salary from (select t.*,管理费,社保费,伙食费 from salary_total t Left join worker_fixitem f on t.workid=f.workid where t.salaryyear=" & Year(Rpt_Date) & " and t.salarymonth=" & Month(Rpt_Date) & " order by t.workid) sf,worker_info wi,(select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,param_dp dp where sf.WorkID=wi.WorkID and sf.WorkID=wd.WorkID and sf.workid like '%" & Rpt_WorkID & "%' and wd.DpID=dp.DpID and wd.DpID Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") sm " & _ "LEFT JOIN worker_changeitem wc ON sm.workid=wc.workid" adoRs.Open querySQL, adoCn Set rpt_PaySalaryDetail.DataSource = adoRs ' Section1 --- 细部 For Each Ctl In rpt_PaySalaryDetail.Sections.Item("Section1").Controls Select Case TypeName(Ctl) Case "RptShape" DSec1_RptShp_Collection.Add Ctl '/细部控件 RptShape/
Case "RptTextBox" Ctl.DataField = adoRs.Fields.Item(0).Name '/先将所有TextBox(RptTextBox) 控件绑定到某一字段, 否则报错!/ DSec1_RptTxt_Collection.Add Ctl '/细部控件 RptTextBox/ End Select Ctl.Left = 0 Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./ Ctl.Height = 250 Ctl.Width = 800 Ctl.Visible = False Next Ctl '以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。 Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/ Set bFmt = New StdDataFormat bFmt.Type = fmtBoolean bFmt.TrueValue = "是" bFmt.FalseValue = "否" Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定 With DSec1_RptShp_Collection.Item(i + 1) .Visible = True
If i = 0 Then .Left = 0 Else .Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width End If .Top = 0
.Height = 250 .Width = 800 .BorderColor = vbWhite .BorderStyle = rptBSSolid .Shape = rptShpRectangle End With ' 细部资料设定 With DSec1_RptTxt_Collection.Item(i + 1) .Visible = True .Height = DSec1_RptShp_Collection.Item(i + 1).Height .Left = DSec1_RptShp_Collection.Item(i + 1).Left .Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30 .Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/ .Font.Name = "" .Font.Size = 8 .Font.Bold = False .Font.Italic = False .Font.Strikethrough = False .Font.Underline = False .ForeColor = vbBlue .DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/ '/可根据字段数据类型设置资料格式/ Select Case adoRs.Fields.Item(i).Type Case adBigInt, adInteger, adSmallInt '/数字/ .Alignment = rptJustifyCenter Case adBoolean '/布尔型字段设定自定义格式/ Set .DataFormat = bFmt .Alignment = rptJustifyCenter Case adSingle, adDouble '/小数/ .DataFormat.Format = "#####0.00" .Alignment = rptJustifyCenter Case adCurrency '/货币/ .DataFormat.Format = "#####0.00" '"###,##0.00" .Alignment = rptJustifyCenter Case adDate, adDBDate, adDBTimeStamp .DataFormat.Format = "Long Date" '/日期、时间/ .Alignment = rptJustifyRight Case Else '/其它,如:文本等/ .Alignment = rptJustifyCenter 'rptJustifyLeft End Select End With Next i
sumSQL = "SELECT worker,sumTS,sumTax,sumPS,sumGL,sumSB,sumCZ,sumAT,sumDT,sumPT FROM " & _ "(select count(st.workid) as worker,format(sum(totalsalary),'#####0.00') as sumTS,format(sum(tax),'#####0.00') as sumTax,format(sum(paysalary),'#####0.00') as sumPS from salary_total st, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi where st.workid=wd.workid and st.workid=wi.workid and st.salaryyear=" & Year(Rpt_Date) & " and st.salarymonth=" & Month(Rpt_Date) & " and st.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") S," & _ "(select iif(sum(管理费) is null,0,sum(管理费)) as sumGL,iif(sum(社保费) is null,0,sum(社保费)) as sumSB from worker_fixitem wf, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,worker_info wi where wf.workid=wd.workid and wf.workid=wd.workid and wf.workid=wi.workid and wf.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") F," & _ "(select iif(sum(厂证) is null,0,sum(厂证)) as sumCZ,iif(sum(加项) is null,0,sum(加项)) as sumAT,iif(sum(减项) is null,0,sum(减项)) as sumDT,iif(sum(罚项) is null,0,sum(罚项)) as sumPT from worker_changeitem wc, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi where wc.workid=wd.workid and wc.workid=wi.workid and salaryyear=" & Year(Rpt_Date) & " and salarymonth=" & Month(Rpt_Date) & " and wc.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") C" sumRs.Open sumSQL, adoCn If Not (sumRs.BOF And sumRs.EOF) Then 'MsgBox "workid=" & sumRs("worker") & ",TS=" & sumRs("sumTS") & ",Tax=" & sumRs("sumTax") & ",GL=" & sumRs("sumGL") & ",CZ=" & sumRs("sumCZ") With rpt_PaySalaryDetail .Sections(5).Controls("Label17").Caption = sumRs("worker") .Sections(5).Controls("Label18").Caption = sumRs("sumTS") .Sections(5).Controls("Label19").Caption = sumRs("sumTax") .Sections(5).Controls("Label20").Caption = sumRs("sumPS") .Sections(5).Controls("Label21").Caption = sumRs("sumGL") .Sections(5).Controls("Label22").Caption = sumRs("sumSB") .Sections(5).Controls("Label23").Caption = sumRs("sumCZ") .Sections(5).Controls("Label24").Caption = sumRs("sumAT") .Sections(5).Controls("Label25").Caption = sumRs("sumDT") .Sections(5).Controls("Label26").Caption = sumRs("sumPT") End With End If rpt_PaySalaryDetail.Sections.Item("Section1").Height = 300
Exit Sub ErrHandler: MsgBox "不存在记录!" & vbCrLf & "错误:" & Err.Number & vbCrLf & "错误信息:" & Err.Description End Sub Private Sub DataReport_QueryClose(Cancel As Integer, CloseMode As Integer) Rpt_Date = Empty Rpt_WorkID = Empty Rpt_DPID = Empty Rpt_FromDate = Empty Rpt_ToDate = Empty Rpt_Enable = Empty
If adoRs.State = adStateOpen Then adoRs.Close If sumRs.State = adStateOpen Then sumRs.Close If adoCn.State = adStateOpen Then adoCn.Close Set adoRs = Nothing Set sumRs = Nothing Set adoCn = Nothing End Sub
这个问题我以前遇过﹐就是汉字的问题﹐你的查询语句中有汉字﹐可能是你的资料表字段名是汉字﹐ 你将字段名改为英文就没有问题了﹐如需要显示汉字就将 ”字段名” as “显示的汉字”﹐因为在ADO环境下运行Jet SQL语句﹐ 对汉字的解析有时是识别不了。这可能是微软的BUG。
还有就是到报表里的数据是不是有为NULL值的数据、或不匹配的数据类型。
在我的计算机上运行没有安装运行都没有问题,但系别的计算机上安装运行,这个报表就打不开了。
Option ExplicitDim adoCn As New ADODB.Connection
Dim adoCm As New ADODB.Command
Dim adoRs As New ADODB.Recordset
Dim sumRs As New ADODB.Recordset
Dim querySQL As String
Dim sumSQL As String
Dim fieldCount As Integer
Private Sub DataReport_Initialize()On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close
adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False"
querySQL = "SELECT d.*,加工资 FROM (Select sd.workid,wi.name,left(dp.fullname,3) as dpname,wd.salary,workday,overflowday,ot,restot,holidayot,holidayrestday,worksalary,overflowsalary,otsalary,restotsalary,holidayotsalary,holidayrestsalary,format(worksalary+overflowsalary+otsalary+restotsalary+holidayotsalary+holidayrestsalary,'#####0.00') as totalsalary " & _
"From salary_detail sd, salary_source ss, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi, param_dp dp Where sd.WorkID = ss.WorkID And sd.WorkID = wd.WorkID And sd.WorkID = wi.WorkID And wd.DpID = dp.DpID And sd.begindate = ss.begindate and year(sd.begindate)=" & Year(Rpt_Date) & " and month(sd.begindate)=" & Month(Rpt_Date) & " and sd.workid like '%" & Rpt_WorkID & "%' and wd.dpid like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & " order by sd.workid) D " & _
"Left Join (select workid,加工资 from worker_changeitem where salaryyear=" & Year(Rpt_Date) & " and salarymonth=" & Month(Rpt_Date) & ") C On d.workid=c.workid"
adoRs.Open querySQL, adoCn
Set rpt_TotalSalaryDetail.DataSource = adoRs
'Const iW = 3 '缇,误差调整
'rpt_PayRoll.ReportWidth = Printer.Width - rpt_PayRoll.LeftMargin - rpt_PayRoll.RightMargin - iW '/设定报表宽度/
Dim X As Integer
Dim inx As Integer
Const ColWidth = 650 '/缇/
Const ColCount = 18 '/可以通过取纸张类型去设定每行打印的字段数量/
fieldCount = 18 '/报表字段列数/ '以下根据控件所在区域(Sections)和所属控件类别等将它们分成若干集合
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/
Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/
Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/
Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/
' Section1 --- 细部
For Each Ctl In rpt_TotalSalaryDetail.Sections.Item("Section1").Controls
Select Case TypeName(Ctl)
'Case "RptLabel"
' DSec1_RptLbl_Collection.Add Ctl '/细部控件 RptLable/
' Ctl.Caption = adoRs.Fields.Item(0).Name
Case "RptShape"
DSec1_RptShp_Collection.Add Ctl '/细部控件 RptShape/
Case "RptTextBox"
Ctl.DataField = adoRs.Fields.Item(0).Name '/先将所有TextBox(RptTextBox) 控件绑定到某一字段, 否则报错!/
DSec1_RptTxt_Collection.Add Ctl '/细部控件 RptTextBox/
'Case "RptLine"
' DSec1_RptLine_Collection.Add Ctl '/细部控件 RptLine/
'Case "RptImage"
' DSec1_RptImg_Collection.Add Ctl '/细部控件 RptImage/
End Select Ctl.Left = 0
Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./
Ctl.Height = 250
Ctl.Width = 650
Ctl.Visible = False
Next Ctl
'以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。
Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/
Set bFmt = New StdDataFormat
bFmt.Type = fmtBoolean
bFmt.TrueValue = "是"
bFmt.FalseValue = "否"
Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定
With DSec1_RptShp_Collection.Item(i + 1)
.Visible = True
If i < ColCount Then
If i = 0 Then
.Left = 0
Else
.Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width
End If
.Top = 0
Else
If i < ColCount * 2 Then
If i = ColCount Then
.Left = 0
Else
.Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width
End If
.Top = 0
Else
If i = ColCount * 2 Then
.Left = 0
Else
.Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width
End If
.Top = 0
End If
End If
.Height = 250
.Width = 650
.BorderColor = vbWhite
.BorderStyle = rptBSSolid
.Shape = rptShpRectangle
End With
' 细部资料设定
With DSec1_RptTxt_Collection.Item(i + 1)
.Visible = True .Height = DSec1_RptShp_Collection.Item(i + 1).Height
.Left = DSec1_RptShp_Collection.Item(i + 1).Left
.Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30
.Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/
.Font.Name = ""
.Font.Size = 8
.Font.Bold = False
.Font.Italic = False
.Font.Strikethrough = False
.Font.Underline = False
.ForeColor = vbBlue
.DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/
'/可根据字段数据类型设置资料格式/
Select Case adoRs.Fields.Item(i).Type
Case adBigInt, adInteger, adSmallInt
'.DataFormat.Format = "###,##0" '/数字/
'.Alignment = rptJustifyRight
.Alignment = rptJustifyCenter
Case adBoolean
Set .DataFormat = bFmt '/布尔型字段设定自定义格式/
.Alignment = rptJustifyCenter
Case adSingle, adDouble
.DataFormat.Format = "#####0.00"
.Alignment = rptJustifyCenter
Case adCurrency
'.DataFormat.Format = "###,##0.00" '/货币/
.DataFormat.Format = "#####0.00"
.Alignment = rptJustifyCenter
Case adDate, adDBDate, adDBTimeStamp
.DataFormat.Format = "Long Date" '/日期、时间/
.Alignment = rptJustifyRight
Case Else '/其它,如:文本等/
'.Alignment = rptJustifyLeft
.Alignment = rptJustifyCenter
End Select End With
Next i
sumSQL = "SELECT format(sum(workday),'#####0.00') as sWD,format(sum(overflowday),'#####0.00') as sOFD,format(sum(ot),'#####0.00') as sOT,format(sum(restot),'#####0.00') as sROT,format(sum(holidayot),'#####0.00') as sHOT,format(sum(holidayrestday),'#####0.00') as sHRD,format(sum(worksalary),'#####0.00') as sWS,format(sum(overflowsalary),'#####0.00') as sOFS,format(sum(otsalary),'#####0.00') as sOTS,format(sum(restotsalary),'#####0.00') as sROTS,format(sum(holidayotsalary),'#####0.00') as sHOTS,format(sum(holidayrestsalary),'#####0.00') as sHRS " & _
"FROM salary_source ss, salary_detail sd, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi " & _
"WHERE sd.workid=ss.workid and sd.workid=wd.workid and sd.workid=wi.workid and sd.begindate=ss.begindate and year(sd.begindate)=" & Year(Rpt_Date) & " and month(sd.begindate)=" & Month(Rpt_Date) & " and sd.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable
sumRs.Open sumSQL, adoCn
If Not (sumRs.BOF And sumRs.EOF) Then
With rpt_TotalSalaryDetail
.Sections(5).Controls("Label22").Caption = sumRs("sWD")
.Sections(5).Controls("Label23").Caption = sumRs("sOFD")
.Sections(5).Controls("Label24").Caption = sumRs("sOT")
.Sections(5).Controls("Label25").Caption = sumRs("sROT")
.Sections(5).Controls("Label26").Caption = sumRs("sHOT")
.Sections(5).Controls("Label27").Caption = sumRs("sHRD")
.Sections(5).Controls("Label28").Caption = sumRs("sWS")
.Sections(5).Controls("Label29").Caption = sumRs("sOFS")
.Sections(5).Controls("Label30").Caption = sumRs("sOTS")
.Sections(5).Controls("Label31").Caption = sumRs("sROTS")
.Sections(5).Controls("Label32").Caption = sumRs("sHOTS")
.Sections(5).Controls("Label33").Caption = sumRs("sHRS")
End With
End If
rpt_TotalSalaryDetail.Sections.Item("Section1").Height = 300
Exit Sub
ErrHandler:
'MsgBox "错误:" & Err.Number & vbCrLf & "错误信息:" & Err.DescriptionEnd Sub
Private Sub DataReport_QueryClose(Cancel As Integer, CloseMode As Integer)
Rpt_Date = Empty
Rpt_WorkID = Empty
Rpt_DPID = Empty
Rpt_FromDate = Empty
Rpt_ToDate = Empty
Rpt_Enable = Empty
If adoRs.State = adStateOpen Then adoRs.Close
If sumRs.State = adStateOpen Then sumRs.Close
If adoCn.State = adStateOpen Then adoCn.Close
Set adoRs = Nothing
Set sumRs = Nothing
Set adoCn = Nothing
End Sub
*******************************Option Explicit
Dim adoCn As New ADODB.Connection
Dim adoCm As New ADODB.Command
Dim adoRs As New ADODB.Recordset
Dim sumRs As New ADODB.Recordset
Dim querySQL As String
Dim sumSQL As String
Dim fieldCount As Integer
Private Sub DataReport_Initialize()
'以下根据控件所在区域(Sections)和所属控件类别等将它们分成若干集合
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/
Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/
Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/
Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/
Dim Ctl As Object
'Const iW = 3 '缇,误差调整
'rpt_PaySalaryDetail.ReportWidth = Printer.Width - rpt_PaySalaryDetail.LeftMargin - rpt_PaySalaryDetail.RightMargin - iW '/设定报表宽度/
Const ColWidth = 800 '/缇/
Const ColCount = 13 '/可以通过取纸张类型去设定每行打印的字段数量/
fieldCount = 13 '/报表字段列数/
'* 注意:
' 报表所显示字段必须与RecordSet所返回的记录一致(报表字段<=返回记录字段), 如果报表显示字段大于返回记录字, 则会引发以下错误:
' Run-time error '3265'
' Item cannot bo found in the collection corresponding to the requested name or ordinal(序数).
On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close
adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False"
querySQL = "SELECT sm.workid,name,dpname,salary,totalsalary,tax,管理费,社保费,厂证,加项,减项,罚项,paysalary " & _
"FROM (select sf.*,wi.name,left(dp.fullname,3) as dpname,wd.salary from (select t.*,管理费,社保费,伙食费 from salary_total t Left join worker_fixitem f on t.workid=f.workid where t.salaryyear=" & Year(Rpt_Date) & " and t.salarymonth=" & Month(Rpt_Date) & " order by t.workid) sf,worker_info wi,(select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,param_dp dp where sf.WorkID=wi.WorkID and sf.WorkID=wd.WorkID and sf.workid like '%" & Rpt_WorkID & "%' and wd.DpID=dp.DpID and wd.DpID Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") sm " & _
"LEFT JOIN worker_changeitem wc ON sm.workid=wc.workid"
adoRs.Open querySQL, adoCn
Set rpt_PaySalaryDetail.DataSource = adoRs
' Section1 --- 细部
For Each Ctl In rpt_PaySalaryDetail.Sections.Item("Section1").Controls
Select Case TypeName(Ctl)
Case "RptShape"
DSec1_RptShp_Collection.Add Ctl '/细部控件 RptShape/
Case "RptTextBox"
Ctl.DataField = adoRs.Fields.Item(0).Name '/先将所有TextBox(RptTextBox) 控件绑定到某一字段, 否则报错!/
DSec1_RptTxt_Collection.Add Ctl '/细部控件 RptTextBox/
End Select Ctl.Left = 0
Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./
Ctl.Height = 250
Ctl.Width = 800
Ctl.Visible = False
Next Ctl
'以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。
Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/
Set bFmt = New StdDataFormat
bFmt.Type = fmtBoolean
bFmt.TrueValue = "是"
bFmt.FalseValue = "否"
Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定
With DSec1_RptShp_Collection.Item(i + 1)
.Visible = True
If i = 0 Then
.Left = 0
Else
.Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width
End If
.Top = 0
.Height = 250
.Width = 800
.BorderColor = vbWhite
.BorderStyle = rptBSSolid
.Shape = rptShpRectangle
End With
' 细部资料设定
With DSec1_RptTxt_Collection.Item(i + 1)
.Visible = True
.Height = DSec1_RptShp_Collection.Item(i + 1).Height
.Left = DSec1_RptShp_Collection.Item(i + 1).Left
.Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30
.Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/
.Font.Name = ""
.Font.Size = 8
.Font.Bold = False
.Font.Italic = False
.Font.Strikethrough = False
.Font.Underline = False
.ForeColor = vbBlue
.DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/
'/可根据字段数据类型设置资料格式/
Select Case adoRs.Fields.Item(i).Type
Case adBigInt, adInteger, adSmallInt '/数字/
.Alignment = rptJustifyCenter
Case adBoolean '/布尔型字段设定自定义格式/
Set .DataFormat = bFmt
.Alignment = rptJustifyCenter
Case adSingle, adDouble '/小数/
.DataFormat.Format = "#####0.00"
.Alignment = rptJustifyCenter
Case adCurrency '/货币/
.DataFormat.Format = "#####0.00" '"###,##0.00"
.Alignment = rptJustifyCenter
Case adDate, adDBDate, adDBTimeStamp
.DataFormat.Format = "Long Date" '/日期、时间/
.Alignment = rptJustifyRight
Case Else '/其它,如:文本等/
.Alignment = rptJustifyCenter 'rptJustifyLeft
End Select
End With
Next i
*******************************
Option Explicit
Dim adoCn As New ADODB.Connection
Dim adoCm As New ADODB.Command
Dim adoRs As New ADODB.Recordset
Dim sumRs As New ADODB.Recordset
Dim querySQL As String
Dim sumSQL As String
Dim fieldCount As Integer
Private Sub DataReport_Initialize()
'以下根据控件所在区域(Sections)和所属控件类别等将它们分成若干集合
Dim DSec1_RptLbl_Collection As New Collection '/细部(Detail)区域 Label(RptLabel) 控件集合/
Dim DSec1_RptShp_Collection As New Collection '/细部(Detail)区域 Shape(RptShape) 控件集合/
Dim DSec1_RptTxt_Collection As New Collection '/细部(Detail)区域 TextBox(RptTextBox) 控件集合, TextBox (RptTextBox) 控件只能绘制到细节(Detail)区域/
Dim DSec1_RptImg_Collection As New Collection '/细部(Detail)区域 Image(RptImage) 控件集合/
Dim Ctl As Object
'Const iW = 3 '缇,误差调整
'rpt_PaySalaryDetail.ReportWidth = Printer.Width - rpt_PaySalaryDetail.LeftMargin - rpt_PaySalaryDetail.RightMargin - iW '/设定报表宽度/
Const ColWidth = 800 '/缇/
Const ColCount = 13 '/可以通过取纸张类型去设定每行打印的字段数量/
fieldCount = 13 '/报表字段列数/
'* 注意:
' 报表所显示字段必须与RecordSet所返回的记录一致(报表字段<=返回记录字段), 如果报表显示字段大于返回记录字, 则会引发以下错误:
' Run-time error '3265'
' Item cannot bo found in the collection corresponding to the requested name or ordinal(序数).
On Error GoTo ErrHandler
If adoCn.State = adStateOpen Then adoCn.Close
adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalaryCount.mdb;Persist Security Info=False"
querySQL = "SELECT sm.workid,name,dpname,salary,totalsalary,tax,管理费,社保费,厂证,加项,减项,罚项,paysalary " & _
"FROM (select sf.*,wi.name,left(dp.fullname,3) as dpname,wd.salary from (select t.*,管理费,社保费,伙食费 from salary_total t Left join worker_fixitem f on t.workid=f.workid where t.salaryyear=" & Year(Rpt_Date) & " and t.salarymonth=" & Month(Rpt_Date) & " order by t.workid) sf,worker_info wi,(select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,param_dp dp where sf.WorkID=wi.WorkID and sf.WorkID=wd.WorkID and sf.workid like '%" & Rpt_WorkID & "%' and wd.DpID=dp.DpID and wd.DpID Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") sm " & _
"LEFT JOIN worker_changeitem wc ON sm.workid=wc.workid"
adoRs.Open querySQL, adoCn
Set rpt_PaySalaryDetail.DataSource = adoRs
' Section1 --- 细部
For Each Ctl In rpt_PaySalaryDetail.Sections.Item("Section1").Controls
Select Case TypeName(Ctl)
Case "RptShape"
DSec1_RptShp_Collection.Add Ctl '/细部控件 RptShape/
Case "RptTextBox"
Ctl.DataField = adoRs.Fields.Item(0).Name '/先将所有TextBox(RptTextBox) 控件绑定到某一字段, 否则报错!/
DSec1_RptTxt_Collection.Add Ctl '/细部控件 RptTextBox/
End Select Ctl.Left = 0
Ctl.Top = 50 '/设定打印同一员工资料时,如存在分行时,行与行之间的距离,反之则是不同员工资料行的距离./
Ctl.Height = 250
Ctl.Width = 800
Ctl.Visible = False
Next Ctl
'以下可根据实际情况通过控制集合的元素来准确有效的控制这些元素所代表的控件。
Dim bFmt As StdDataFormat '/定义布尔(Boolean)型字段的资料格式/
Set bFmt = New StdDataFormat
bFmt.Type = fmtBoolean
bFmt.TrueValue = "是"
bFmt.FalseValue = "否"
Dim i As Integer
For i = 0 To fieldCount - 1 '/指定打印字段数量/
' 细部单元格设定
With DSec1_RptShp_Collection.Item(i + 1)
.Visible = True
If i = 0 Then
.Left = 0
Else
.Left = DSec1_RptShp_Collection.Item(i).Left + DSec1_RptShp_Collection.Item(i).Width
End If
.Top = 0
.Height = 250
.Width = 800
.BorderColor = vbWhite
.BorderStyle = rptBSSolid
.Shape = rptShpRectangle
End With
' 细部资料设定
With DSec1_RptTxt_Collection.Item(i + 1)
.Visible = True
.Height = DSec1_RptShp_Collection.Item(i + 1).Height
.Left = DSec1_RptShp_Collection.Item(i + 1).Left
.Top = DSec1_RptShp_Collection.Item(i + 1).Top + 30
.Width = DSec1_RptShp_Collection.Item(i + 1).Width '/字体属性/
.Font.Name = ""
.Font.Size = 8
.Font.Bold = False
.Font.Italic = False
.Font.Strikethrough = False
.Font.Underline = False
.ForeColor = vbBlue
.DataField = adoRs.Fields.Item(i).Name '/重新绑定字段/
'/可根据字段数据类型设置资料格式/
Select Case adoRs.Fields.Item(i).Type
Case adBigInt, adInteger, adSmallInt '/数字/
.Alignment = rptJustifyCenter
Case adBoolean '/布尔型字段设定自定义格式/
Set .DataFormat = bFmt
.Alignment = rptJustifyCenter
Case adSingle, adDouble '/小数/
.DataFormat.Format = "#####0.00"
.Alignment = rptJustifyCenter
Case adCurrency '/货币/
.DataFormat.Format = "#####0.00" '"###,##0.00"
.Alignment = rptJustifyCenter
Case adDate, adDBDate, adDBTimeStamp
.DataFormat.Format = "Long Date" '/日期、时间/
.Alignment = rptJustifyRight
Case Else '/其它,如:文本等/
.Alignment = rptJustifyCenter 'rptJustifyLeft
End Select
End With
Next i
sumSQL = "SELECT worker,sumTS,sumTax,sumPS,sumGL,sumSB,sumCZ,sumAT,sumDT,sumPT FROM " & _
"(select count(st.workid) as worker,format(sum(totalsalary),'#####0.00') as sumTS,format(sum(tax),'#####0.00') as sumTax,format(sum(paysalary),'#####0.00') as sumPS from salary_total st, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi where st.workid=wd.workid and st.workid=wi.workid and st.salaryyear=" & Year(Rpt_Date) & " and st.salarymonth=" & Month(Rpt_Date) & " and st.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") S," & _
"(select iif(sum(管理费) is null,0,sum(管理费)) as sumGL,iif(sum(社保费) is null,0,sum(社保费)) as sumSB from worker_fixitem wf, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd,worker_info wi where wf.workid=wd.workid and wf.workid=wd.workid and wf.workid=wi.workid and wf.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") F," & _
"(select iif(sum(厂证) is null,0,sum(厂证)) as sumCZ,iif(sum(加项) is null,0,sum(加项)) as sumAT,iif(sum(减项) is null,0,sum(减项)) as sumDT,iif(sum(罚项) is null,0,sum(罚项)) as sumPT from worker_changeitem wc, (select t.* from worker_transfer t,(select workid,max(usedate) as md from worker_transfer where usedate<#" & DateAdd("m", 1, Rpt_Date) & "# group by workid) nt where t.workid=nt.workid and t.usedate=md) wd, worker_info wi where wc.workid=wd.workid and wc.workid=wi.workid and salaryyear=" & Year(Rpt_Date) & " and salarymonth=" & Month(Rpt_Date) & " and wc.workid like '%" & Rpt_WorkID & "%' and wd.dpid Like '" & Rpt_DPID & "%' and wi.joindate between #" & Rpt_FromDate & "# and #" & Rpt_ToDate & "# and wi.enable=" & Rpt_Enable & ") C" sumRs.Open sumSQL, adoCn
If Not (sumRs.BOF And sumRs.EOF) Then
'MsgBox "workid=" & sumRs("worker") & ",TS=" & sumRs("sumTS") & ",Tax=" & sumRs("sumTax") & ",GL=" & sumRs("sumGL") & ",CZ=" & sumRs("sumCZ")
With rpt_PaySalaryDetail
.Sections(5).Controls("Label17").Caption = sumRs("worker")
.Sections(5).Controls("Label18").Caption = sumRs("sumTS")
.Sections(5).Controls("Label19").Caption = sumRs("sumTax")
.Sections(5).Controls("Label20").Caption = sumRs("sumPS")
.Sections(5).Controls("Label21").Caption = sumRs("sumGL")
.Sections(5).Controls("Label22").Caption = sumRs("sumSB")
.Sections(5).Controls("Label23").Caption = sumRs("sumCZ")
.Sections(5).Controls("Label24").Caption = sumRs("sumAT")
.Sections(5).Controls("Label25").Caption = sumRs("sumDT")
.Sections(5).Controls("Label26").Caption = sumRs("sumPT")
End With
End If rpt_PaySalaryDetail.Sections.Item("Section1").Height = 300
Exit Sub
ErrHandler:
MsgBox "不存在记录!" & vbCrLf & "错误:" & Err.Number & vbCrLf & "错误信息:" & Err.Description
End Sub
Private Sub DataReport_QueryClose(Cancel As Integer, CloseMode As Integer)
Rpt_Date = Empty
Rpt_WorkID = Empty
Rpt_DPID = Empty
Rpt_FromDate = Empty
Rpt_ToDate = Empty
Rpt_Enable = Empty
If adoRs.State = adStateOpen Then adoRs.Close
If sumRs.State = adStateOpen Then sumRs.Close
If adoCn.State = adStateOpen Then adoCn.Close
Set adoRs = Nothing
Set sumRs = Nothing
Set adoCn = Nothing
End Sub
你将字段名改为英文就没有问题了﹐如需要显示汉字就将 ”字段名” as “显示的汉字”﹐因为在ADO环境下运行Jet SQL语句﹐ 对汉字的解析有时是识别不了。这可能是微软的BUG。