Excel ADODB查询日期 [英] Excel ADODB Query Dates

问题描述

我正在尝试查询Excel列中的日期(例如，这是7月，然后仅从列中检索7月的日期)我试图声明当前月和上个月，但仍然无法检索数据.我试图将查询表格式化为(m/d/yyyy)多种格式以匹配查询，但仍然没有运气.请提供任何帮助，在这里提供以下帮助

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

推荐答案

尝试之间的条件运算符.并使用英镑符号.

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

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