SQL/VBA - if/then/else for day of year when recordset has Leap Years and non Leap Years -

good morning,

here issue. have form user selects date want work with. there, in vba, store entered date in variable (ytddate) , day of year date stored variable (dayofyear). these variable values used in following sql statement:

    sqlytdcounts = "select count(x.event_unique_id) total,                      year(occurrence_date) year " & _                    "from (select distinct event_unique_id, occurrence_date                      events datepart('y',occurrence_date) <=" &                      dayofyear & _                     ")  x group year(occurrence_date) having                      (((year([occurrence_date]))>='" & _                     datepart("yyyy", ytddate) - 10 & "') )" 

so happening recordset retrieved day of year each [occurrence_date] <= day of year date user selected on form.

the sql works great, non leap years, leap years getting day of year before user selected date. example:

user selects july 23,2017

dayofyear = 204

but leap year july 23rd 205th day of year.

in head, solution seems simple enough: recordset check see if year of occurrence of these years (2004,2008,2012,2016) , if day of year of occurrence_date occurrence >= 60 (the reason being can't check if leap year because ly , nonly have same day of year until 60).

if both of these criteria met, <=" & dayofyear &_ part of sql statement <=" & (dayofyear + 1) & _

if non of criteria met sql statement remain is.

now, how translate vba stuck. have tried using if else statements sql statement hasn't worked (i pretty sure not doing correctly) =]

i apologize if super convoluted , not making sense, , input/suggestions/assistance appreciated.

thanks, mark

try method using dateadd handles leap years correctly:

sqlytdcounts = _     "select count(*) total, year(occurrence_date) year " & _     "from " & _     "    (select distinct event_unique_id, occurrence_date " & _     "    events " _     "    datediff('d', dateadd('yyyy', " & year(ytddate) & " - year(occurrence_date), occurrence_date), occurrence_date) <= 0) x " & _      "group year(occurrence_date) " & _     "having year([occurrence_date]) >= " & year(ytddate) & " - 10)" 

edit2:

sqlytdcounts = _     "select count(*) total, year(occurrence_date) year " & _     "from " & _     "    (select distinct event_unique_id, occurrence_date " & _     "    events " & _     "    datediff('d', dateadd('yyyy', " & year(ytddate) & " - year(occurrence_date), occurrence_date), #" & format(ytddate, "yyyy\/mm\/dd") & "#) >= 0) x " & _      "group year(occurrence_date) " & _     "having year([occurrence_date]) >= " & year(ytddate) & " - 10"