Query MS Access using Excel VBA, SQL BETWEEN dates query

Question

all--

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?

Thanks in advance!

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!

Nerds of Technology · Accepted Answer · 2017-12-07 22:50:54Z

1

In Access

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

is the same as

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

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

answered Dec 7, 2017 at 22:50

Nerds of Technology

1977 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jacqueline Trout Over a year ago

Thanks for the input! I will use the parentheses going forward, but I modified my code using them, then tried swapping out the BETWEEN with the >= AND <= arguments, and received the following error: Run-Time error '-2147217900 (80040e14)': Syntax error (missing operator) in query expression 'AdHocReport.[CxName] = 'Client Name' AND (AdHocReport.[ConsolidationDate] >= #11/1/2017# AND (AdHocReport.[ConsolidationDate] <= #11/15/2017#)'. Andy ideas?

Gustav Over a year ago

As printed here, it isn't correct. And you have forgot the formatting of the text expressions for date values.

Jacqueline Trout Over a year ago

Hi, Gustav! The statement above is a reflection of what was read; it's an error message, not the code that caused the error message. That's why it reads 'Client Name' instead of '" & CName & "'. But thank you for reviewing!

Gustav · Accepted Answer · 2017-12-08 10:14:23Z

0

In ADO you must use the ISO sequence in string expressions for date values:

... "' AND AdHocReport.[ConsolidationDate] BETWEEN #" & Format(FromDate, "yyyy\/mm\/dd") & "# AND #" & Format(ToDate, "yyyy\/mm\/dd") & "#" ...

answered Dec 8, 2017 at 10:14

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

4 Comments

Jacqueline Trout Over a year ago

Thanks for the input! I updated the query to format the date parameters but it still does not pull the records I've identified that fit this query. Could the problem be with my dimensioning or defined variables?

Jacqueline Trout Over a year ago

Apologies for not understanding, but what do you mean by 'generated' SQL?

Gustav Over a year ago

The SQL your code generates, or builds or concatenates.

Jacqueline Trout Over a year ago

Thanks, Gustav! I was able to determine the error was not in my original code at all but in the recordset.

Collectives™ on Stack Overflow

Query MS Access using Excel VBA, SQL BETWEEN dates query

2 Answers 2

3 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Linked

Related