Question

vb+access sql 关于日期范围查询提问

编的很乱，这个是本日收费查询 ，还想编一个本月查询、本年查询但是月份和年份老写不对，请大侠指点

Dim a1, a2 As String
a1 = Format(Now, "yyyy-mm-dd")
a2 = a1 & " 23:59:59"
a1 = a1 & " 00:00:00"
Dim b1, b2 As Date
If IsDate(a1) And IsDate(a2) Then
b1 = CDate(a1)
b2 = CDate(a2)
Else
MsgBox "日期值错误!"
Exit Sub
End If
strSql = "select sum(本次交款) as aaa from 收费明细 where 收费日期 between #" & b1 & "# and #" & b2 & "#"
Set g_rs = g_db.OpenRecordset(strSql)
补充一下，数据库中 收费日期 保存的是长日期时间格式 例: "2014/1/15 10:15:23"

Answer 1

1、获取“年”范围变量
sDate=Format$(Date,"YYYY") & "-01-01 00:00:00"
eDate=Format$(Date,"YYYY") & "-12-31 23:59:59"

2、获取“月”范围变量
sDate=Format$(Date,"YYYY-MM") & "-01 00:00:00"
eDate=DateAdd("d",-1,Format$(Date,"YYYY") & "-" & Format$(Date,"MM")+1 & "-01") & " 23:59:59"

其它的SQL统计语句不用变化,只要把这两个变量替代就可以实现统计了.

Answer 2

本月的
strSql = "select sum(本次交款) as aaa from 收费明细 where 收费日期>= #" & format(a1,"yyyy-mm-01") & "# and 收费日期<#" & format(cdate(foramt(a1,'yyyy-mm-01"))+32,"yyyy-mm-01") & "#"
本年的
strSql = "select sum(本次交款) as aaa from 收费明细 where year(收费日期)=" & format(a1,"yyyy")