1

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 & "'"
Community
  • 1
  • 1
INOH
  • 365
  • 2
  • 9
  • 27
  • This question has an answer that gives some information on how excel stores dates. It may help. http://stackoverflow.com/questions/31492086/adodb-excel-database-date-format-issue/31492716#31492716 – MatthewD Jul 19 '15 at 16:27

1 Answers1

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 & "#"
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • 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 – INOH Jul 19 '15 at 22:21
  • 1
    Glad we got it worked out. There are always many ways to do it. Keep coding. – MatthewD Jul 19 '15 at 22:23