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

@Ashleedawg. Thank you for your comment and time, but Peh's answer solved the issue. For info, the debug showed 01/02/18 and 28/02/18 - as far as the dates on the missing records, i dont know. Takes quite a while to look it over. But it has been solved - And again, thank you for you attention :) — Boxx
– Boxx, Commented Mar 13, 2018 at 13:39
@Boxx as ashleedawg pointed out my answer is not correct, because I just looked at your criteria instead of using my brain. Please consider choosing his answer as solution so I can delete mine. — Pᴇʜ
– Pᴇʜ, Commented Mar 13, 2018 at 13:46
@Pᴇʜ - Thanks, my SQL OCD's going nuts here since I once spent days troubleshooting missing vs. overlapped data because of a similar issue. :-) — ashleedawg
– ashleedawg, Commented Mar 13, 2018 at 13:51
@ashleedawg of course you are completely right. I just didn't think about it, because the criteria was already in his question. That's always an issue if wrong things are already on paper it's too easy to believe what you read. — Pᴇʜ
– Pᴇʜ, Commented Mar 13, 2018 at 13:55
@ashleedawg - You are absolutely right - i'm too speedy! :) That's what you get from trying to comment and react to everything, while trying to fix your solution i guess. Sorry! On another note - It is all working now! - Your fix works brilliantly, and i get the correct number, while also taking into account the issues you presented. You are a lifesaver! :) Thank you! — Boxx
– Boxx, Commented Mar 13, 2018 at 14:02

Md. Suman Kabir · Accepted Answer · 2018-03-13 14:41:41Z

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

Parfait · Accepted Answer · 2018-03-13 14:08:15Z

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

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

Collectives™ on Stack Overflow

SQL query in vba by last month down to the second

2 Answers 2

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

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

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

Comments

Comments

Related