Dim SCtr As String
Dim xCtr As String
Dim jCtr As String
Dim lCtr As String
If TxtCustomer.Text <> "" Then
SCtr = "And A.CustomerNumber= " & TxtCustomer.Text
End If
xCtr = IIf(Trim(sDepositGroup1) = 0, "''", Replace(Trim(sDepositGroup1), " ", ""))
jCtr = IIf(Trim(sDepositGroup2) = 0, "''", Replace(Trim(sDepositGroup2), " ", ""))
lCtr = IIf(Trim(sDepositGroup3) = 0, "''", Replace(Trim(sDepositGroup3), " ", ""))
sCmd = "If(A.DepositType='" & sPaymentType1 & "',if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & sPaymentType1 & ","
sCmd = sCmd & " If(A.DepositType='" & sPaymentType2 & "',if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & sPaymentType2 & ","
sCmd = sCmd & " If(A.DepositType='" & sPaymentType3 & "',if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & sPaymentType3 & ","
sCmd = sCmd & " If(A.DepositType='" & sPaymentType4 & "',if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & sPaymentType4 & ","
sCmd = sCmd & " If(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & xCtr & ","
sCmd = sCmd & " If(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & jCtr & ","
sCmd = sCmd & " If(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & lCtr & " "
sSql = " SELECT A.DepositID,A.TransDate,SUM(if(e.TransStatus=1,E.AMOUNT,-1*E.AMOUNT)) AS Balance,c.CustomerName,A.Description,If(A.TransID=0,"""",A.TransID) as ReceiptID,D.operatorName,"
sSql = sSql & sCmd
sSql = sSql & " FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.DepositGroupID "
sSql = sSql & " LEFT Join Customers C on C.CustomerNumber=A.CustomerNumber "
sSql = sSql & " LEFT Join Operators D on D.OperatorID=A.CashierID "
sSql = sSql & " LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.DepositGroupID=E.DepositGroupID "
sSql = sSql & " Where A.TransDate>=E.TransDate "
sSql = sSql & SCtr
sSql = sSql & " GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber "
sSql = sSql & " Order by A.CustomerNumber,A.DepositGroupID,A.TransDate " Set RS = oCmn.ExecRS(sSql, DCN)
If RS.RecordCount <= 0 Then
MsgBox "No match found!", vbOKOnly + vbExclamation, "Customers Report"
RS.Close
Set RS = Nothing
Exit Sub
End If
Set oSetting = New CSetting
oSetting.Init DCN
Set objCurrency = New CCurrency
objCurrency.Init sProvider, sSalesDataSource & MASTERDATAFILE, DCN Set arv3 = New rptCustomerwiseSalesReport
Set arv3.adoCustomerData.Recordset = RS
With arv3
.Label29.Caption = sPaymentType1
.Label30.Caption = sPaymentType2
.Label31.Caption = sPaymentType3
.Label32.Caption = sPaymentType4
.Label33.Caption = xCtr
.Label34.Caption = jCtr
.Label35.Caption = lCtr
.txtPayment1.DataField = sPaymentType1
.txtPayment2.DataField = sPaymentType2
.txtPayment3.DataField = sPaymentType3
.txtPayment4.DataField = sPaymentType4
.txtGroup1.DataField = xCtr
.txtGroup2.DataField = jCtr
.txtGroup3.DataField = lCtr
.CusCurrency = Trim$(objCurrency.CurrencyPrefix)
.CusFormat = objCurrency.GetFormat(True)
'.txtcomname.Text = Trim$(oSetting.CompanyName)
.txtPrintDate.Text = Format(Now, "dd/mmm/yyyy hh:mm")
.txtFromToDate = "FROM " & Format(dtpFromTrans.Value, "dd / mmm / yyyy") & " TO " & Format(dtpToTrans.Value, "dd / mmm / yyyy")
.Printer.RenderMode = 1
End With
frmArvView2.RunReport arv3
frmArvView2.Show vbModal
Set arv3 = Nothing
RS.Close
Set RS = Nothing
REPORT这里,一直显示不出sPaymentType1,sPaymentType2,sPaymentType3,sPaymentType4,xCtr,jCtr,lCtr.
因为是动态的,所以,没有直接在REPORT设计中给它加DataField的值。
特来请教,数据可以显示。
txtPayment1-txtPayment4的VISIBLE为TRUE
什么报表 控件?
sPaymentType1 没取到值? 还是 .Label29.Caption 没显示值?
If(A.DepositType='" & sPaymentType2 & "',if(A.TransStatus=1,1,-1)*A.Amount,"""") as " & sPaymentType2 & ","
在DATAFIELD中直接放Cash也是不可以显示,是不是别名的问题?
这样试试
是不是别名的问题?
应该不是
估计是赋值问题
.txtPayment1.DataField= 'sPaymentType1'
SELECT A.DepositID,A.TransDate,SUM(if(e.TransStatus=1,E.AMOUNT,-1*E.AMOUNT)) AS Balance,c.CustomerName,A.Description,if(A.TransID=0,"",A.transid) as Receipt,D.operatorName,If(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash, If(A.DepositType='AMEX',if(A.TransStatus=1,1,-1)*A.Amount,"") as AMEX, If(A.DepositType='MASTERS',if(A.TransStatus=1,1,-1)*A.Amount,"") as MASTERS, If(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as VISA, If(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Deposit, If(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as SchoolFee, If(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as '' FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.DepositGroupID LEFT Join Customers C on C.CustomerNumber=A.CustomerNumber LEFT Join Operators D on D.OperatorID=A.CashierID LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.DepositGroupID=E.DepositGroupID Where A.TransDate>=E.TransDate
GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber Order by A.CustomerNumber,A.DepositGroupID,A.TransDateRESULT:
1, '2010-12-14 12:44:36', 800.0000, 'freda', 'Normal', '', 'SysAdm', '800.0000', '', '', '', '800.0000', '', ''
2, '2010-12-14 12:44:45', 490.0000, 'freda', 'Paid Trans ', '1', 'SysAdm', '', '', '', '', '-310.0000', '', ''
4, '2010-12-14 12:46:00', 440.0000, 'freda', 'Paid Trans ', '3', 'SysAdm', '', '', '', '', '-50.0000', '', ''
6, '2010-12-14 14:01:35', 417.5000, 'freda', 'Paid Trans ', '4', 'SysAdm', '', '', '', '', '-22.5000', '', ''
7, '2010-12-14 14:02:02', 392.5000, 'freda', 'Paid Trans ', '5', 'SysAdm', '', '', '', '', '-25.0000', '', ''
10, '2010-12-14 14:50:28', 442.5000, 'freda', 'Normal', '', 'SysAdm', '', '', '', '', '50.0000', '', ''
11, '2010-12-14 15:26:39', 362.1000, 'freda', 'Paid Trans ', '8', 'SysAdm', '', '', '', '', '-80.4000', '', ''
12, '2010-12-14 15:27:59', 162.1000, 'freda', 'Paid Trans ', '9', 'SysAdm', '', '', '', '', '-200.0000', '', ''
14, '2010-12-15 09:15:50', 212.1000, 'freda', 'Normal', '', 'SysAdm', '50.0000', '', '', '', '50.0000', '', ''
15, '2010-12-15 09:46:53', 712.1000, 'freda', 'Normal', '', 'SysAdm', '500.0000', '', '', '', '500.0000', '', ''
16, '2010-12-15 11:12:20', 679.3000, 'freda', 'Paid Trans ', '10', 'SysAdm', '', '', '', '', '-32.8000', '', ''
17, '2010-12-15 11:13:24', 729.3000, 'freda', 'Normal', '', 'SysAdm', '', '', '50.0000', '', '50.0000', '', ''
18, '2010-12-15 11:13:34', 779.3000, 'freda', 'Normal', '', 'SysAdm', '', '', '', '50.0000', '50.0000', '', ''
19, '2010-12-15 14:32:58', 512.3000, 'freda', 'Redeem Deposit', '13', 'SysAdm', '', '', '', '', '-267.0000', '', ''
20, '2010-12-15 14:35:47', 312.3000, 'freda', 'Redeem Deposit', '', 'SysAdm', '-200.0000', '', '', '', '-200.0000', '', ''
21, '2010-12-15 14:49:56', 412.3000, 'freda', 'Top-up Deposit', '', 'SysAdm', '100.0000', '', '', '', '100.0000', '', ''
22, '2010-12-15 14:50:05', 312.3000, 'freda', 'Redeem Deposit', '', 'SysAdm', '-100.0000', '', '', '', '-100.0000', '', ''
25, '2010-12-15 14:58:55', 212.3000, 'freda', 'Return Customer Deposit', '', 'SysAdm', '-100.0000', '', '', '', '-100.0000', '', ''
26, '2010-12-15 14:59:05', 412.3000, 'freda', 'Top-up Customer Deposit', '', 'SysAdm', '200.0000', '', '', '', '200.0000', '', ''
28, '2010-12-15 15:59:59', 462.3000, 'freda', 'top-up Deposit', '', 'SysAdm', '50.0000', '', '', '', '50.0000', '', ''
29, '2010-12-15 16:00:43', 512.3000, 'freda', 'top-up Deposit', '', 'SysAdm', '50.0000', '', '', '', '50.0000', '', ''
3, '2010-12-14 12:45:20', 500.0000, 'freda', 'Fee', '', 'SysAdm', '500.0000', '', '', '', '', '500.0000', ''
5, '2010-12-14 12:46:00', 225.0000, 'freda', 'Paid Trans ', '3', 'SysAdm', '', '', '', '', '', '-275.0000', ''
13, '2010-12-14 15:27:59', 40.0000, 'freda', 'Paid Trans ', '9', 'SysAdm', '', '', '', '', '', '-185.0000', ''
9, '2010-12-14 14:50:04', 100.0000, 'gemma', 'Normal', '', 'SysAdm', '100.0000', '', '', '', '100.0000', '', ''
23, '2010-12-15 14:54:21', 200.0000, 'gemma', 'Top-up Normal', '', 'SysAdm', '100.0000', '', '', '', '100.0000', '', ''
24, '2010-12-15 14:55:07', 100.0000, 'gemma', 'Return Normal', '', 'SysAdm', '-100.0000', '', '', '', '-100.0000', '', ''
8, '2010-12-14 14:49:42', 100.0000, 'gemma', 'Fee', '', 'SysAdm', '', '', '', '100.0000', '', '100.0000', ''
27, '2010-12-15 15:15:15', 0.0000, 'gemma', 'Return School Fee', '', 'SysAdm', '-100.0000', '', '', '', '', '-100.0000', ''
30, '2010-12-15 16:01:04', 100.0000, 'edmond wong', 'Top-up Deposit', '', 'SysAdm', '100.0000', '', '', '', '100.0000', '', ''
31, '2010-12-15 16:01:13', 0.0000, 'edmond wong', 'Return Deposit', '', 'SysAdm', '-100.0000', '', '', '', '-100.0000', '', ''
sPaymentType1,sPaymentType2,sPaymentType3,sPaymentType4,xCtr,jCtr,lCtr分别对应着上面的Cash,AMEX,MASTERS,Visa,Deposit,SchoolFee,''全部是英文
.txtPayment1.DataField = "'" & sPaymentType1中的值 & "'"
.txtPayment1.DataField = "'" & sPaymentType1中的值 & "'"
你测试一下是否需要单引号
在报表中,设置TXT控件数据源为也要为sPaymentType1的值
If(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as '' 另外建议把所有的“”改成NULL,否则你的是数字文本混排。
If(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
改为
If(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,null) as Cash,
我决定为你们2个各散100分。