1

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.

Giacomo Rabino
  • 13
  • 3

1 Answers1

2

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.
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Hi! Thank you for your help. I understood the logic behind what you wrote, i am only struggling implementing the vba code and calling it from the query, as i never worked with vba and i have to learn it. I have created a public module with your summerdays code, but when i try to implement it in the sql code, i get an error saying "Function "SummerDays" not defined in the expression". I'll post the whole query in the main post now. – Giacomo Rabino Jul 01 '19 at 08:09
  • @GiacomoRabino since you are new to VBA, I have added some instructions to my answer describing how to create a public module to house the function definition. – Lee Mac Jul 01 '19 at 11:56
  • Thanks man, now it works. You really helped me, i hope to learn fast vba as it seems it could really juice up my sql code. cheers – Giacomo Rabino Jul 01 '19 at 14:13