SQL query in vba by last month down to the second

Question

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

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

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

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?

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 ;")

Answer 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.

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

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.

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.

---

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:

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 ;")