使用Excel VBA，SQL BETWEEN日期查询来查询MS Access [英] Query MS Access using Excel VBA, SQL BETWEEN dates query

问题描述

全部-

我正在尝试使用SQL查询，使用以下代码将记录从Access数据库中提取到Excel VBA用户窗体列表框中:

I'm attempting to use an SQL query to pull records from an Access db into an Excel VBA userform listbox using the following code:

Sub FillLBBillIDs()
'build bill ID list box with bill IDs available in database, based on client and/or date range
'<---------------------------------------------------Dimension all variables
Dim con As Object, cmd As Object, rst As Object
Dim Path As String, CName As String
Dim FromDate As Date, ToDate As Date
Dim X As Long, Y As Long
'<---------------------------------------------------Define Default Variables
X = 0
CName = AuditParametersFRM.CBOCxName.Value
FromDate = AuditParametersFRM.DTPFrom.Value
ToDate = AuditParametersFRM.DTPTo.Value
'<---------------------------------------------------Define Access connection
Set con = CreateObject("ADODB.Connection"): Set cmd = CreateObject("ADODB.Command"): Set rst = CreateObject("ADODB.RecordSet"):
Path = Sheets("AuditTool").Range("B2").Value
'<---------------------------------------------------Open Access connection
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";Persist Security Info=False;"
con.ConnectionTimeout = 0: con.CommandTimeout = 0: con.Open: cmd.CommandTimeout = 0: Set cmd.ActiveConnection = con
'<---------------------------------------------------Find all bill IDs in the database which match the selected client and
'<---------------------------------------------------are within the consolidated date range
rst.Open "SELECT DISTINCT AdHocReport.[BillID] FROM AdHocReport WHERE AdHocReport.[CxName] = '" & CName & "' AND AdHocReport.[ConsolidationDate] BETWEEN #" & FromDate & "# AND #" & ToDate & "#", con, 1, 3
On Error Resume Next
rst.MoveLast
rst.MoveFirst
Y = 0
Y = rst.RecordCount
AuditToolFRM.LBBillIDs.Clear
If Not Y = 0 Then
    Do Until rst.EOF
'<---------------------------------------------------Build the listbox with the acquired information
        With AuditToolFRM.LBBillIDs
            .AddItem
            .List(X, 0) = rst![BillID]
            X = X + 1
        End With
        rst.MoveNext
    Loop
End If
rst.Close
On Error GoTo 0
con.Close
End Sub

如果我使用大于参数，则此代码可以正常工作，因此:

This code works just fine if I use a greater than argument, thusly:

rst.Open "SELECT DISTINCT AdHocReport.[BillID] FROM AdHocReport WHERE AdHocReport.[CxName] = '" & CName & "' AND AdHocReport.ConsolidationDate > #" & FromDate & "#", con 1, 3

我已经浏览了本网站上的所有相关问题，找不到任何有效的方法.有什么想法吗?

I've gone through all the pertinent questions on this site and can't find anything that works. Any ideas?

提前谢谢！

12/08/2017 12:54

12/08/2017 12:54

我做了更多的测试，看来大于查询也不起作用；它会拉取所有符合第一个条件的记录，而忽略第二个条件，即使使用括号将第二个条件括起来也是如此.这告诉我问题肯定在查询的日期部分中.任何帮助将不胜感激！

I've done more testing and it appears that the greater than query isn't working either; it's pulling all records that meet the first criteria whilst ignoring the second, even when using parentheses to enclose the second. This tells me that the issue is definitely in the date portion of the query somehow. Any help is appreciated greatly!

推荐答案

访问中

DATE_FIELD BETWEEN #2/2/2012# AND #2/4/2012# 

与

DATE_FIELD >=#2/2/2012# AND <=#2/4/2012#

当您在日期范围语法周围加上另一个AND字样时.

When you have another AND put parathesis around the date range syntax.

rst.Open "SELECT DISTINCT AdHocReport.[BillID] FROM AdHocReport WHERE AdHocReport.[CxName] = '" & CName & "' AND (AdHocReport.[ConsolidationDate] BETWEEN #" & FromDate & "# AND #" & ToDate & "#)", con, 1, 3

这篇关于使用Excel VBA，SQL BETWEEN日期查询来查询MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！