SQL查询VBA中的日期 [英] SQL Query between dates in VBA
问题描述
使用excel和访问数据库在VBA中创建一个sql查询有问题。在mi VBA代码中,我有两个数据变量,其中包含以下内容:
I've a problem making a sql query in VBA using excel and an access database. In mi VBA code I've two data variables with this content:
DateMin = 31/07/2014 22:00:00
DateMin = 31/07/2014 22:00:00
DateMax = 01/08/2014 06:00:00
DateMax = 01/08/2014 06:00:00
而且我有一个访问数据库,带有一个带有日期字段的许多数据。我试图从数据库中提取数据,日期在我的两个变量之间,我编码这个:
And I have an access database with many data with a date field. I'm trying to extract from the database, data with dates between my two variables and I'm coding this:
sql = "SELECT Date FROM Table WHERE Date BETWEEN #" & DateMin & "# AND #" & DateMax & "#
但不起作用。结果不正确,日期不在我的两个日期。
But doesn't works. Results are incorrect, with dates that aren't between my two dates.
但是,如果我更改句子并为此代码
However if I change the sentence and code this
sql = "SELECT Date FROM Table WHERE Date > #" & DateMin & "#
它的工作原理!提供的日期高于DateMin的数据,但是我没有DateMax来停止它。我认为问题在我的第一个代码的第二部分,因为如果我的代码
It works! Gives data with higher dates than DateMin but I haven't DateMax to stop it. I think the problem is in the second part of my first code, because if I code
sql = "SELECT Date FROM Table WHERE Date < #" & DateMax & "#
我没有数据!为什么不给我的数据低于DateMax?因为这样我认为没有使用BETWEEN句子。
I have no data! Why doesn't give me data lower than DateMax? Because of that I think doesn't works the BETWEEN sentence.
对于我的英语,我正在努力解释更好我知道谢谢。
Sorry for my english, i'm trying to explain better I know. Thanks.
推荐答案
您应该始终使用明确的日期格式,当您有一个日期格式 00/00/0000
,Access必须猜测这是否是 dd / mm / yyyy
(英国)或 mm / dd / yyyy
(美国)
You should always use an unambiguous date format. When you have a date formated 00/00/0000
, Access has to guess whether that's dd/mm/yyyy
(British) or mm/dd/yyyy
(American).
您的 DateMin
( 31/07/2014 22:00:00
)只能解释为 dd / mm / yyyy hh:nn:ss
,(31是无效的月份它必须是一天),所以这是Access将使用的格式。
Your DateMin
(31/07/2014 22:00:00
) can only be interpreted as dd/mm/yyyy hh:nn:ss
, (31 is an invalid month, so it must be a day) so this is the format that Access will use.
但是,您的 DateMax
( 01/08/2014 06:00:00
)正在以美国格式解释为 2014年1月8日
而不是 2014年8月1日
。
However, your DateMax
(01/08/2014 06:00:00
) is being interpreted in American format - as 8th January 2014
rather than 1st August 2014
.
e最简单的方法是以ISO格式( yyyymmdd
)提供您的日期,或者将该月份作为短字提供( dd mmm yyyy hh: mm:ss
- 例如 2014年8月1日06:00:00
)
The easiest way to get around this is to supply your dates in ISO format (yyyymmdd
) or supply the month as a short word (dd mmm yyyy hh:mm:ss
- eg 01 Aug 2014 06:00:00
)
这篇关于SQL查询VBA中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!