c11 = "签订日期 >=#" & Me.Text_date_start & " # " + " And " + "签订日期 <=#" & Me.Text_date_fin & " # "
Private Sub Command_bbsc_Click()
Dim c1 As String
Dim c2 As String
Dim c3 As String
Dim c5 As String
'If (Me.Combo_xmlb = "全部" And Me.Text_jsdw = "全部" And Me.Combo_isjieqing = "全部" And Me.Combo_xuanze = "全部") Then
' DoCmd.OpenReport "工程项目费用明细表", acViewReport, , "建设单位<>'' and 1=1 and 1=1 "
'End If
'1、C1项目类别——>C2建设单位——C3是否结清——C4是否选中
If Me.Combo_xmlb = "全部" Then
c1 = "1=1"
Else: c1 = "类别='" & Me.Combo_xmlb & "' "
End If
If Me.Text_jsdw = "全部" Then
c2 = "1=1"
Else: c2 = "建设单位='" & Me.Text_jsdw & "' "
End If
If Me.Combo_isjieqing = "全部" Then
c3 = "1=1"
Else: c3 = "结清=" & Me.Combo_isjieqing & " "
End If
If Me.Combo_xuanze = "全部" Then
c4 = "1=1"
Else: c4 = "选中=" & Me.Combo_xuanze & " "
End If
c5 = c1 + " And " + c2
c6 = c5 + " And " + c3
c7 = c6 + " And " + c4
'Me.Combo_xmlb = c6
DoCmd.OpenReport "工程项目费用明细表", acViewReport, , c7
End Sub
目标:简单建立表链接,实现往来账务统计提示。
一、工程基本资料
不同工程项目合同金额
二、工程款支付统计
统计支付次数、时间、金额、
三、应收款
SELECT [合同].[项目名称] FROM 合同 INNER JOIN 付款 ON [合同].[CONTRACTID]=[付款].[合同ID] WHERE [合同].[CONTRACTID]=[付款].[合同ID];
SELECT [合同].[CONTRACTID], [合同].[项目名称] FROM 合同 INNER JOIN 付款 ON [合同].[CONTRACTID]=[付款].[合同ID] WHERE [合同].[CONTRACTID]=[付款].[合同ID];
SELECT [合同].[项目名称] FROM 合同;
UPDATE 合同 INNER JOIN 付款 ON 合同.CONTRACTID = 付款.合同ID SET 合同.D1次付款日期 = [付款].[日期], 合同.D1次付款金额 = [付款].[支付金额]
WHERE (((付款.付款次号)=1) AND ((付款.合同ID)=[合同].[CONTRACTID]));
报表:自动生成序号
access报表自动输出行号:
1、在报表的主体中增加一个文本框控件,名字可以为“序号”
2、切换到设计视图,选中刚才建立的“序号”控件,在右键菜单中选属性
3、在控件的属性表中选择''数据"选项卡,在”控件来源“中写上"=1"
4、在“运行总和”一栏中选择“在全部之上”
表换行:报表中:选中字段——属性——可以扩大