赵刚老师《Access经典用变量条件分类汇总数据库》推荐网友们学习

望江婷 · 发表于 2011-3-8 16:58

原帖由 无辣味水煮鱼 于 2011-3-8 16:55 发表

Zhaogang1960老师，你太低调了，我也很想做你这样的excel盲，估计很多人都想做你这样的excel盲。

同感，唉人品跃人耳目，感动

zhaogang1960 · 发表于 2011-3-8 16:58

原帖由 无辣味水煮鱼 于 2011-3-8 16:55 发表

Zhaogang1960老师，你太低调了，我也很想做你这样的excel盲，估计很多人都想做你这样的excel盲。

不信吧，我都不信，请看上午我问的：
做了一个，但我不知道公式是怎么插入的，总是显示提示更新的对话框，用了Application.AskToUpdateLinks = False也不行，试试看吧
顺便告诉我一下：='D:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\005\[2010申报表.xlsm]客户基本信息'!$C$5是怎么插入到单元格的
http://club.excelhome.net/viewth ... p;page=1#pid4694110

zhaogang1960 · 发表于 2011-3-8 17:04

有点疑问：
access数据库中的“日期”字段是日期格式(yyyy-mm-dd)，包含“日”，这样的分组汇总可能不符合楼主的要求，下面代码中是按照数据库中的日期格式输出的
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub CommandButton1_Click()
Dim s$, t$, v$, i%, SQL$
For i = 1 To 4
      If Me.Controls("ComboBox" & i).Value <> "" Then
         t = t & "," & Me.Controls("Label" & i).Caption
         v = v & "," & Me.Controls("Label" & i).Caption
      Else
         t = t & ",null"
      End If
Next
t = t & ",费用类别,费用明细,sum(金额)"
v = v & ",费用类别,费用明细"
If ComboBox1.Value <> "" Then
      s = s & " and " & Label1.Caption & "='" & ComboBox1.Value & "'"
      If ComboBox2.Value <> "" Then s = s & " and month(" & Label2.Caption & ")=val(" & Split(ComboBox2.Value, "-")(1) & ")"
Else
      If ComboBox2.Value <> "" Then s = s & " and " & Label1.Caption & "='" & Split(ComboBox2.Value, "-")(0) & "年' and month(" & Label2.Caption & ")=val(" & Split(ComboBox2.Value, "-")(1) & ")"
End If
For i = 3 To 4
      If Me.Controls("ComboBox" & i).Value <> "" Then s = s & " and " & Me.Controls("Label" & i).Caption & "='" & Me.Controls("ComboBox" & i).Value & "'"
Next
If s = "" Then Exit Sub
SQL = "select " & Mid(t, 2) & " from 费用汇总单 where " & Mid(s, 6) & " group by " & Mid(v, 2)
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
With Sheets("查询")
      .[A4:H65536].ClearContents
      .Range("b4").CopyFromRecordset rs
End With
End Sub

zhaogang1960 · 发表于 2011-3-8 17:06

附件

材料汇总.rar (31.95 KB, 下载次数: 85)

无辣味水煮鱼 · 发表于 2011-3-8 17:10

赵老师，你真的是一个很热心的人！同时也向你学了很多东西，谢谢。

望江婷 · 发表于 2011-3-8 17:18

谢谢赵老师，我的天呢，我的附件数据库数据写错了，让赵老师受累了，我看看试一下你的代码，下面的才是我准备上船的数据库。

数据库.rar (7.71 KB, 下载次数: 24)

望江婷 · 发表于 2011-3-8 17:21

因为我的错，给赵老师增添了不必要的脑力劳动，致歉，对不起了

望江婷 · 发表于 2011-3-8 17:33

谢谢赵老师，想要的结果满足了，就是按月汇总这个条件不能实现了，我学习学习，看看实在不行就把这个条件去了，让你受累了

zhaogang1960 · 发表于 2011-3-8 17:41

原帖由 望江婷 于 2011-3-8 17:18 发表
谢谢赵老师，我的天呢，我的附件数据库数据写错了，让赵老师受累了，我看看试一下你的代码，下面的才是我准备上船的数据库。
884743

没有发现有什么区别，下面按照月份汇总：
Private Sub CommandButton1_Click()
Dim s$, t$, v$, i%, SQL$
For i = 1 To 4
      If Me.Controls("ComboBox" & i).Value <> "" Then
'          If i <> 2 Then t = t & "," & Me.Controls("Label" & i).Caption Else t = t & ",year(" & Me.Controls("Label" & i).Caption & ")&month(" & Me.Controls("Label" & i).Caption & ")"
'          If i <> 2 Then v = v & "," & Me.Controls("Label" & i).Caption Else v = v & ",year(" & Me.Controls("Label" & i).Caption & ")&month(" & Me.Controls("Label" & i).Caption & ")"
         If i <> 2 Then t = t & "," & Me.Controls("Label" & i).Caption Else t = t & ",month(" & Me.Controls("Label" & i).Caption & ")"'纯按月份，上面是年连接月
         If i <> 2 Then v = v & "," & Me.Controls("Label" & i).Caption Else v = v & ",month(" & Me.Controls("Label" & i).Caption & ")"
      Else
         t = t & ",null"
      End If
Next
t = t & ",费用类别,费用明细,sum(金额)"
v = v & ",费用类别,费用明细"
If ComboBox1.Value <> "" Then
      s = s & " and " & Label1.Caption & "='" & ComboBox1.Value & "'"
      If ComboBox2.Value <> "" Then s = s & " and month(" & Label2.Caption & ")=val(" & Split(ComboBox2.Value, "-")(1) & ")"
Else
      If ComboBox2.Value <> "" Then s = s & " and " & Label1.Caption & "='" & Split(ComboBox2.Value, "-")(0) & "年' and month(" & Label2.Caption & ")=val(" & Split(ComboBox2.Value, "-")(1) & ")"
End If
For i = 3 To 4
      If Me.Controls("ComboBox" & i).Value <> "" Then s = s & " and " & Me.Controls("Label" & i).Caption & "='" & Me.Controls("ComboBox" & i).Value & "'"
Next
If s = "" Then Exit Sub
SQL = "select " & Mid(t, 2) & " from 费用汇总单 where " & Mid(s, 6) & " group by " & Mid(v, 2)
Set rs = New ADODB.Recordset

rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
With Sheets("查询")
      .[A4:H65536].ClearContents
      .Range("b4").CopyFromRecordset rs
End With
End Sub

[ 本帖最后由 zhaogang1960 于 2011-3-8 17:42 编辑 ]

zhaogang1960 · 发表于 2011-3-8 17:43

附件

材料汇总.rar (30.17 KB, 下载次数: 98)

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

[分享] 赵刚老师《Access经典用变量条件分类汇总数据库》推荐网友们学习