CODEWITHSUNDEEP
excelvba
INOH
INOH

Reputation: 385

Excel ADODB Query Dates

I am trying to Query Excel Column for Dates ( for example this is Jul, then only retrieve Jul dates from column) I have tried to declared a current month and previous month but still am unable to retrieve data. I have tried to format query sheet as (m/d/yyyy) multiple formats to match query but still no luck. please any help much appreciated here is the code below

Dim cnStr As String
Dim rs As ADODB.Recordset
Dim query As String
Dim var1
Dim var2
Dim fileName As String
Dim CurrMonth As String, PrevMonth As String
CurrMonth = Format$(Date, "m/d/yyyy")
PrevMonth = Format$(Date - Day(Date), "m/d/yyyy")
fileName = "**********\DB1.xlsm"

    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & fileName & ";" & _
           "Extended Properties=Excel 12.0"

query = "SELECT * FROM [test1$A:B] WHERE [test]>'" & PrevMonth & "' AND [test]<'" & CurrMonth & "'"

Upvotes: 1

Views: 4281

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Try a between conditional operator. And use the pound signs.

BETWEEN #7/1/2008# And #7/31/2008#

query = "SELECT * FROM [test1$A:B] WHERE [test] BETWEEN #" & PrevMonth & "# And #" & CurrMonth & "#"

Upvotes: 3

Related Questions