这位“兰色幻想”老师还真是苦口婆心,在教VBA的过程中,连SQL也都附带教一遍,很有心!
- '按条件筛选---数字条件
- '直接用对比符号,><=
- Sub 按条件筛选1()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 销售单价 > 100"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- '按条件筛选---字符条件
- Sub 按条件筛选2()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 物品名称 ='挡泥板'"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- '按条件筛选---日期条件
- Sub 按条件筛选3()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 出库日期 =#2005-1-4#"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- '按条件筛选---区间条件
- Sub 按条件筛选4()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 出库日期 between #2005-1-4# and #2005-1-10#"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- '按条件筛选--多个条件
- '用and,OR连接
- Sub 按条件筛选5()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 出库日期 between #2005-1-4# and #2005-1-10# and 销售单价>100"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- ''按条件筛选--模糊条件
- '%表示任意多个字符,_(下划线)表示单个占位符
- Sub 按条件筛选6()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 物品名称 like '%扶手%'"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- ''按条件筛选--插入变量
- Sub 按条件筛选7()
- Dim sql As String
- Dim data As New 数据库
- Dim sr
- sr = "挡泥板"
- sql = "Select * from [sheet1$] Where 物品名称 ='" & sr & "'"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- ''按条件筛选--在字符串组里
- Sub 按条件筛选8()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where 物品名称 in('车衣','扶手箱')"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- ''按条件筛选--借用函数
- Sub 按条件筛选9()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from [sheet1$] Where left(物品代码,3)='028'"
- data.执行筛选 data.Excel数据库, sql
- End Sub
- '筛选方式是指结果的样式
- '1 筛选全部字段
- '*表示全部字段
- Sub 筛选全部字段()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '2 筛选指定字段
- '在from前面列出要显示的所有字段,如果要跳过的用""""
- Sub 显示指定字段()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select """",出库日期,"""",物品代码,"""",规格,单位 from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '3 筛选不重复的
- 'Distinct 字段名 筛选不重复的记录
- Sub 不重复筛选()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select Distinct """",物品代码 from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '4 筛选前N个
- 'TOP N 只显示前N个记录
- Sub 筛选前10个() '按个数筛选
- Dim sql As String
- Dim data As New 数据库
- sql = "Select top 10 * from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- 'Top N percent 可以显示前百分之N的记录
- Sub 筛选百分之N() '按百分比筛选
- Dim sql As String
- Dim data As New 数据库
- sql = "Select top 30 Percent * from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '5 格式化显示的结果
- '可以对筛选的字段用函数进行进一步的处理和格式化
- Sub 格式化字段() '按百分比筛选
- Dim sql As String
- Dim data As New 数据库
- sql = "Select ID,Format(出库日期,""yyyy-mm-dd"") from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '6 对筛选后的结果排序
- Sub 排序() '
- 'Desc降序
- 'Asc升序
- Dim sql As String
- Dim data As New 数据库
- sql = "Select * from ChuKu Order by 出库日期 asc,销售单价 desc"
- data.执行筛选 data.Access数据库, sql
- End Sub
- Sub 筛选销售数量前10() '
- Dim sql As String
- Dim data As New 数据库
- sql = "Select Top 10 * from ChuKu Order by 出库日期 asc,销售单价 desc"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '7 分组显示
- 'Group by 可以配合函数进行分组求和,分组求最大值等.
- Sub 分组() '
- Dim sql As String
- Dim data As New 数据库
- sql = "Select """","""",物品代码,"""","""","""",sum(出库数量) from ChuKu group by 物品代码"
- data.执行筛选 data.Access数据库, sql
- End Sub
- Sub 按条件显示分组记录() '
- Dim sql As String
- Dim data As New 数据库
- sql = "Select """","""",物品代码,"""","""","""",sum(出库数量) from ChuKu group by 物品代码 HAVING sum(出库数量)>=3"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '1 SUM函数求和,count计数
- Sub 求和()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select sum(出库数量),count(出库数量) from ChuKu where 物品代码='0270001'"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '2 left,right,mid,instr,format文本函数
- Sub 文本()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select 物品代码, ""左三位:"" & left(物品代码,3),right(物品代码,4),mid(物品代码,2,2),instr(物品代码,""1"") from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '3 year,month,day,datediff,DateSerial日期函数
- Sub 日期()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select 出库日期, year(出库日期),month(出库日期),day(出库日期),DateDiff(""m"",出库日期,now) from ChuKu"
- data.执行筛选 data.Access数据库, sql
- End Sub
- '4 max,min,first,last 最值函数
- Sub 最小值()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select 物品名称,min(销售金额) from ChuKu group by 物品名称"
- data.执行筛选 data.Access数据库, sql
- End Sub
- Sub 最新值()
- Dim sql As String
- Dim data As New 数据库
- sql = "Select 物品名称,last(销售金额) from ChuKu group by 物品名称"
- data.执行筛选 data.Access数据库, sql
- End Sub