CODEWITHSUNDEEP
mysqlsqlms-accessms-access-2000
Giacomo Rabino
Giacomo Rabino

Reputation: 13

Counting days in a product life excluding the summers

I am trying to write an ms access SQL query in order to calculate the number of days that have passed since a date in two year periods: "summer" (from 1/6 to 30/09) and non-summer (from 1/1 to 30/05 and from 1/10 to 31/12).

This date is the last "measure" of the product and it could be as old as two years. Then I would like to know, as today, how many days of summer and vice-versa how many days of nonsummer has passed since the product has been measured.

ie. the date is 31/10/2017, I'd like two columns to show summer days passed (=30days*4months in 2018+ 28days in 2019 = 148days) and non-summer days passed ( 7months between Nov17 and may17+ 8 months from Ott18 to May 19 = 450days)

I have been trying using IIF cycles like that one: (ANNI is a variable that means in which year we are 0=this year, 1 past year, etc)

days of non-summer

IIF(ANNI=0; "150"; IIF(ANNI=1;   
IIF(G2.DATA_PRODUZ<#01/06/2018#;DateDiff("d",[G2]! 
[DATA_PRODUZ],#01/06/2018#)+90+150;                              
IIF(G2.DATA_PRODUZ>#30/08/18#;DateDiff("d",[G2]! 
[DATA_PRODUZ],#31/12/2018#)+150));"etc");)

But it won't work and it does it would only work this summer. I'd accept any ideas.

Upvotes: 1

Views: 185

Answers (1)

Lee Mac
Lee Mac

Reputation: 16025

Perhaps not the most efficient method, but certainly the most readable might be to define a VBA function (in a public module) such as:

Function SummerDays(ByVal dateFrom As Date, ByVal dateTo As Date) As Long
    While dateFrom < dateTo
        If dateFrom >= DateSerial(Year(dateFrom), 6, 1) And dateFrom <= DateSerial(Year(dateFrom), 9, 30) Then
            SummerDays = SummerDays + 1
        End If
        dateFrom = dateFrom + 1
    Wend
End Function

And then call this from your query:

SummerDays(G2.DATA_PRODUZ, Date())

Then, for the non-summer days, you could either define another function with an inverse if statement, or subtract the number of summer days from the total number of days between the two dates, e.g.:

DateDiff("d",G2.DATA_PRODUZ, Date()) - SummerDays(G2.DATA_PRODUZ, Date())

To create a public module in which to define the above function:

  • Open the VBA IDE using Alt+F11
  • Insert a new public module by going to Insert > Module or Alt, I, M
  • Copy the above function definition into the Module, below the Option statements.
  • Save the Module with an appropriate name.

Upvotes: 2

Related Questions