vba中的SQL查询在上个月下降到第二个

I have searched for a solution to my issue for quite some time, but cannot seem to find an answer that works. I have a table that contains influx of cases to the different lawyers based on date and casetype. Keep in mind that I'm a rookie when it comes to VBA and SQL

我已经在很长一段时间内搜索了我的问题的解决方案，但似乎无法找到有效的答案。我有一张表格，根据日期和案例类型向不同的律师提供案件。请记住，在VBA和SQL方面，我是新手

What I'm trying to do:

我想做什么：

I need to sort the massive amount of cases into how many cases each lawyer gets based on dates, which in this case is recurring last month. Essentially I need to be able to press a button every month and then the data for the previous month is automatically retrieved and placed in an excel workbook. All of this has actually succeeded, except for the following problem

我需要将大量案例分类为每个律师根据日期获得的案件数量，在这种情况下，上个月会重复出现。基本上我需要能够每个月按一个按钮，然后自动检索上个月的数据并将其放在Excel工作簿中。除了以下问题之外，所有这些实际上都成功了

Problem:

问题：

Whenever I run the macro it doesn't retrieve the full amount. For instance, in February I know there were 159 new cases, but the formula only finds 155. Now I can get the correct result, by prompting an inputbox for a startdate and enddate and entering the format DD/MM/YY HH/MM/SS - But I would like to remove the inputbox part, so I don't have to type anything. The code should automatically take the last month in its enterity. For Feb. it would look like from 01/02/18 00:00:01 to 28/02/18 23:59:59

每当我运行宏时，它都不会检索全部数量。例如，在2月我知道有159个新案例，但公式只找到155.现在我可以通过提示输入框的startdate和enddate并输入格式DD / MM / YY HH / MM /得到正确的结果SS - 但我想删除输入框部分，所以我不必输入任何内容。代码应该自动进入其肠道的最后一个月。 2月份的情况从01/02/18 00:00:01到28/02/18 23:59:59

I assume the problem is because my current formula, doesn't extend all way and therefore excludes the hours, minutes and seconds - or something similar.

我认为问题是因为我目前的公式并没有全程延伸，因此不包括小时，分钟和秒 - 或类似的东西。

I am also pretty confident it can be fixed with the dateadd function, I just don't know how. How do you specify a Startdate and Enddate to include the entire month, down to the very first and last second?

我也非常有信心可以使用dateadd函数修复它，我只是不知道如何。如何指定Startdate和Enddate以包括整个月，直至第一个和最后一个？

Might be a very easy fix, but it's beyond me

可能是一个非常简单的修复，但它超出了我

Code:

码：

Dim Startdate As Date
Dim Enddate As Date

Startdate = DateSerial(Year(Now), Month(Now) - 1, 1)
Enddate = DateSerial(Year(Now), Month(Now), 0)

Debug.Print Startdate, Enddate

Set rs = conn.Execute("Select [Jurist], [OpretDato], [Tilgang] From [dbo].[TilgangOgAfgangAfSagerTilgangIPeriodenView]" & _
"Where [OpretDato] Between '" & Startdate & "' And '" & Enddate & "' and (Jurist not in ('BF','MLT','NL') or Jurist is null)" & _
"Order by [Jurist] ASC ;")

2 个解决方案

#1

Your four records are probably missing from the last day, because 2018-02-28 = 2018-02-28 00:00:00 = midnight at the beginning of February 28th.

您的四个记录可能在最后一天丢失，因为2018-02-28 = 2018-02-28 00:00:00 = 2月28日午夜。

Another problem with your formula: think what will happen in January 2019... Your formula (and others answers here) will return:

你的公式的另一个问题：想想2019年1月会发生什么......你的公式（以及其他答案）将返回：

Year = 2019  Month = 0  Day = 1

Obviously, that won't work. You can't just "subtract 1 from the month" to consistently get a previous month.

显然，这是行不通的。你不能只是“从月中减1”来持续获得前一个月。

Also, you shouldn't specify a end time of 23:59:59 and start time of 00:00:01, since you're skipping over 12 minutes a year ...as well as the month of December.

此外，您不应指定23:59:59的结束时间和00:00:01的开始时间，因为您每年跳过12分钟......以及12月份。

---

This is the correct way to handle "previous month" criteria:

EndDate = DateSerial(Year(Now), Month(Now), 1)
StartDate = DateSerial(Year(EndDate - 1), Month(EndDate - 1), 1)

and then your SQL criteria would be:

然后你的SQL标准是：

WHERE ([RecordDateTime] >= StartDate And [RecordDateTime] < EndDate)

Note that the = is NOT included in the second half of the criteria.

请注意，=不包括在标准的后半部分。

---

Your adjusted code:

您调整后的代码：

Set rs = conn.Execute("Select [Jurist], [OpretDato], [Tilgang] " & _
    "From [dbo].[TilgangOgAfgangAfSagerTilgangIPeriodenView]" & _
    "Where [OpretDato] >= '" & Startdate & "' And [OpretDato] < '" & _
    Enddate & "' and (Jurist not in ('BF','MLT','NL') or Jurist is null)" & _
    "Order by [Jurist] ASC ;")

#2

Alternatively, use DateAdd() which follows in line with @ashleedawg's point of using the strictly less, <, than on the current month for EndDate (consistent across any year/month). Additionally, below uses a ADO parameterization to separate SQL code from VBA data and not string interpolation (an industry best practice).

或者，使用DateAdd（），其符合@ ashleedawg使用严格较小的<，而不是当前月份的EndDate（在任何年/月中保持一致）。此外，下面使用ADO参数化将SQL代码与VBA数据分开，而不是字符串插值（行业最佳实践）。

Dim StartDate As Date, EndDate As Date
Dim strSQL As String

EndDate = DateAdd("d", -Day(Date) + 1, Date)
StartDate = DateAdd("m", -1, EndDate)

Debug.Print StartDate, EndDate

' PREPARED STATEMENT
strSQL = "SELECT [Jurist], [OpretDato], [Tilgang] " & _
         " FROM [dbo].[TilgangOgAfgangAfSagerTilgangIPeriodenView]" & _
         " WHERE [OpretDato] >= ? AND [OpretDato] < ?" & _
         " AND (Jurist NOT IN ('BF','MLT','NL') OR Jurist IS NULL)" & _
         " ORDER BY [Jurist] ASC ;"

' COMMAND OBJECT (BINDING PARAMETERS)
With cmd
    .ActiveConnection = conn    ' CONNECTION OBJECT
    .CommandText = strSQL
    .CommandType = adCmdText

    .Parameters.Append .CreateParameter("s_param", adDate, adParamInput, , StartDate)
    .Parameters.Append .CreateParameter("e_param", adDate, adParamInput, , EndDate)
End With

' BIND TO RECORDSET
Set rst = cmd.Execute
...