EXCEL(VBA)~SQL 经典写法范本汇集(五)
时 间:2012-04-11 09:16:33
作 者:欢乐小爪 ID:20149 城市:杭州
摘 要:EXCEL(VBA)~SQL 经典写法范本汇集(五)
正 文:
select 原料名称,物流号,sum(重量) as 重量Kg,sum(金额) as 金额元 from [原料入库表$A2:H18] where 原料名称='甜味素' and 日期>=#2012-03-01# and 日期<=#2012-03-09# group by 原料名称,物流号
Gavin(549199361) 20:13:16
表的查询区域写法
Dim rng As Range
Set rng = [a4].Resize([a1].CurrentRegion.Rows.Count - 3,[a1].CurrentRegion.Columns.Count)
sql = "Select " & Join(brr, ",") & " from [Sheet1$" & rng.Address(0, 0) & "]"
---------------------------------------------------------------------------------------------
sub hh()
If OptionButton1.Value = True Then :YLRKRow = yuanliaoruku.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料入库表$A2:H" & YLRKRow
End If
If OptionButton2.Value = True Then :YLCKRow = yuanliaochuku.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料出库表$A2:H" & YLCKRow
End If
If OptionButton3.Value = True Then : YLKCRow = yuanliaokuchu.Range("A65536").End(xlUp).row
chaxunbiaoquyu = "原料出库表$A2:H" & YLKCRow
End If
If CDylName.Text <> "所有原料" Then chaxunziju = "原料名称='" & CDylName.Text & "'"
If CDwuliuName.Text <> "所有物流号" Then chaxunziju = chaxunziju & " and 物流号='" & CDwuliuName.Text & "'"
chaxunmingcheng = "原料入出库及库存表汇总查询"
查询模块.yuanliaochaxunmokuai
------------------------------------------------------------------
Public chaxunmingcheng As String
Public chaxunziju As String
Public chaxunbiaoquyu As String
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim spath, SQL, Crt As String
'需引用 microsoft activeX Data Objects 2.8 library
Private Sub Open_database()
' On Error Resume Next
CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
End Sub
Private Sub Close_database()
' On Error Resume Next
RST.Close
CNN.Close
Set RST = Nothing
Set CNN = Nothing
End Sub
Sub exe_cnn()
' On Error Resume Next
Dim R%, F%
RST.Open SQL, CNN, adOpenKeyset, adLockOptimistic
R = 3
For F = 0 To RST.Fields.Count - 1
Sheets("Temp").Cells(R, F + 1) = RST.Fields(F).Name
Next F
Sheets("Temp").Range("A4").CopyFromRecordset RST
Sheets("Temp").Range("A25").CopyFromRecordset CNN.Execute(SQL)
Call Close_database
End Sub
Sub yuanliaochaxunmokuai()
Sheets("Temp").Select
Sheets("Temp").Rows.Clear
Dim tt As String
Select Case chaxunmingcheng
Case "原料入出库及库存表汇总查询"
Call Open_database
SQL = "select 原料名称,物流号,sum(重量) as 重量Kg,sum(金额) as 金额元 from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"group by 原料名称,物流号" 'ORDER BY子句将按升序排列
Case "原料入出库及库存表以原料名称排序明细查询"
Call Open_database
SQL = "select * from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"ORDER BY 原料名称"
Case "原料入出库及库存表以物流号排序明细查询"
Call Open_database
SQL = "select * from [" & chaxunbiaoquyu & "]" & _
" where " & chaxunziju & "日期 between #" & UserF原料管理.CDqDate.Value & "# and #" & UserF原料管理.CDzDate.Value & "# " & _
"ORDER BY 物流号"
Case Else
Exit Sub
End Select
Call exe_cnn
Sheets("Temp").Range("A2").Value = "查询日期为" & Now() & "----" & Left(chaxunbiaoquyu, 5) & Right(chaxunmingcheng, 4)
Sheets("Temp").Range("A1").Value = "查询条件:日期为" & UserF原料管理.CDqDate.Value & "~" & UserF原料管理.CDzDate.Value & " " & chaxunziju
End Sub
相关索引:上一节 完
Access软件网官方交流QQ群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access小作品】简单的待...(04.30)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
学习心得
最新文章
- access软件网站长张志简介 (05.05)
- 【Access小作品】简单的待办任...(04.30)
- 从另一个ACCESS数据库批量导入...(04.29)
- Access日期格式的数据导出Ex...(04.28)
- ACCESS精华集锦资料.CHM(04.25)
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)