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屋！