老师你在哪里?一个汇总查询问题!(问题已解决)

太可思 · 发表于 2008-1-25 11:27

实例中第一个按钮commandbutton1是一个成功的利用日期和编号条件查询,与大家共享!

我的请求:

输入相应编码,用第二个按钮commandbutton2得到汇总查询.详见实例中要求.先向老师致谢了!

lgIqoO8I.rar (24.02 KB, 下载次数: 15)

[此贴子已经被作者于2008-2-15 17:01:54编辑过]

BobPan · 发表于 2008-2-1 14:01

ref:

Sub fu()
Dim CNN As New ADODB.Connection
Dim pthStr As String
Dim SQL As String
Dim Icount As Integer, iRow As Integer
    iRow = [D14].End(xlUp).Row + 2
    pthStr = ThisWorkbook.Path & "\分录表.mdb"
    CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & pthStr
    SQL = "select 摘要,科目编码,总账科目,明细科目,借方金额,贷方金额 from flb where 号 like'" & Sheets("记账凭证").Range("H4").Value & "' and 月 like'" & Month(Sheets("记账凭证").Range("E4").Value) & "'"
    Cells(iRow, 3).CopyFromRecordset CNN.Execute(SQL)

    iRow = [D14].End(xlUp).Row + 1
    SQL = "select '合计','','','',sum(借方金额),sum(贷方金额) from flb where 号 like'" & Sheets("记账凭证").Range("H4").Value & "' and 月 like'" & Month(Sheets("记账凭证").Range("E4").Value) & "'"
    Cells(iRow, 3).CopyFromRecordset CNN.Execute(SQL)
    CNN.Close
End Sub

太可思 · 发表于 2008-2-12 20:44

首先向楼上表示最诚挚的谢意!

解决了我的问题,但还有问题,请打开附件,谢谢!

7g9qVw3i.rar (21.07 KB, 下载次数: 0)

BobPan · 发表于 2008-2-14 08:14

QUOTE:

以下是引用太可思在2008-2-12 20:44:41的发言：

首先向楼上表示最诚挚的谢意!

解决了我的问题,但还有问题,请打开附件,谢谢!

烦请将示例汇总数据填入附表供参考！

太可思 · 发表于 2008-2-14 10:40

Sub fu()
Dim CNN As New ADODB.Connection
Dim pthStr As String
Dim SQL As String
Dim Icount As Integer, iRow As Integer
     pthStr = ThisWorkbook.Path & "\分录表.mdb"
    CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & pthStr
     iRow = [D14].End(xlUp).Row
    SQL = "select '合计','','','',sum(借方金额),sum(贷方金额) from flb where 科目编码 like'" & Sheets("记账凭证").Range("D8").Value & "' and 月 like'" & Month(Sheets("记账凭证").Range("E4").Value) & "'"
    Cells(iRow, 3).CopyFromRecordset CNN.Execute(SQL)
    CNN.Close
End Sub
'请在这个代码的基础上改代码

[此贴子已经被作者于2008-2-14 10:49:54编辑过]

太可思 · 发表于 2008-2-14 10:46

太可思 · 发表于 2008-2-14 11:03

希望能得到的效果：见红字部分手工输入1001点按钮查询得到如下结果（1001不消失）

[此贴子已经被作者于2008-2-14 13:34:31编辑过]

BobPan · 发表于 2008-2-14 14:55

直接在D8单元格中输入1001即汇总数据，如还有问题，请继续提出！

yH4g390o.rar (24.45 KB, 下载次数: 13)

[此贴子已经被作者于2008-2-18 13:27:59编辑过]

太可思 · 发表于 2008-2-14 23:37

Sub fu(ByRef Row_Idx As Byte, ByRef Criteria1 As String, ByRef Criteria2 As Byte)
    Dim CNN As New ADODB.Connection
    Dim pthStr As String
    Dim SQL As String
    Dim Icount As Integer

    pthStr = ThisWorkbook.Path & "\分录表.mdb"

    CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & pthStr
    SQL = "Select sum(贷方金额) from flb where 科目编码 = '" & Criteria1 & "' and 月 =" & Criteria2 & ""
    Debug.Print SQL
    Application.EnableEvents = False
    Cells(Row_Idx, 8).CopyFromRecordset CNN.Execute(SQL)
    Application.EnableEvents = True

    CNN.Close
End Sub

从心底里说一句你是[em17],

还有我要在D8位置输入代码550213的时候,要求能汇总借方金额也是1815

BobPan · 发表于 2008-2-15 08:35

ref:(更新代码即可)

Sub fu(ByRef Row_Idx As Byte, ByRef Criteria1 As String, ByRef Criteria2 As Byte)
    Dim CNN As New ADODB.Connection
    Dim pthStr As String
    Dim SQL As String
    Dim Icount As Integer

    pthStr = ThisWorkbook.Path & "\分录表.mdb"

    CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & pthStr
    SQL = "Select sum(借方金额),sum(贷方金额) from flb where 科目编码 = '" & Criteria1 & "' and 月 =" & Criteria2 & ""
    Debug.Print SQL
    Application.EnableEvents = False
    Cells(Row_Idx, 7).CopyFromRecordset CNN.Execute(SQL)
    Application.EnableEvents = True

    CNN.Close
End Sub

		自动登录	找回密码
密码			免费注册