Excel ADODB Query Dates

Question

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 & "'"

This question has an answer that gives some information on how excel stores dates. It may help. stackoverflow.com/questions/31492086/… — MatthewD
– MatthewD, Commented Jul 19, 2015 at 16:27

MatthewD · Accepted Answer · 2015-07-19 23:12:59Z

3

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 & "#"

edited Jul 19, 2015 at 23:12

answered Jul 19, 2015 at 16:30

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

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

2 Comments

INOH Over a year ago

That did the trick, another way i found to work was to format query worksheet column to custom text and then setup my calendar control to format as text(7-17-2015). I like MatthewD way better, thanks a great deal, 2 days of trial and error, should have posted 2 days ago then trial and error..lol thanks again

MatthewD Over a year ago

Glad we got it worked out. There are always many ways to do it. Keep coding.

Collectives™ on Stack Overflow

Excel ADODB Query Dates

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related