Query filter for this quarter and next (1 Viewer)

[mib1019]

I have a table with a field called air_week. I can calculate a text value of it's 'Period' using the datepart function (y and q), and formatted as YYYY-Qx (where x is the quarter number). I have used that Period data in several places throughout the database.

What I'm looking for in a query is the criteria to filter out everything earlier than the current quarter, so the records would include this quarter and all future.

So if the air_week date is 12/7/20 (next Monday's date, the query would return all the Mondays back to 10/5/20 and forward from 12/7/20.

Any ideas? I'm sure there are plenty of solutions.

Thanks in advance!
MIB1019

 

[plog]

Your example doesn't fit your explanation:

the records would include this quarter and all future.
...
So if the air_week date is 12/7/20 the query would return all the Mondays back to 10/5/20 and forward from 12/7/20.

This quarter begin on 10/1/2020, why are records of the first four dates not included? Why did you mention a specific day of the week in your example, but not mention it in your explanation?

 

[Galaxiom]

Beware using your quarter calculation to select in any query. It is very important to understand the meaning of SARGable in queries.

If you convert the dates in the field to the quarter string then select on that result, your query will have to process every record in the table to make the selection and won't be able to use the index that should be on the field. You won't notice the performance penalty until you have a considerable number of records but it will steadily bog down as record numbers climb.

However it is done, your query should directly compare the date field with a calculated date range on the right hand side of the Where clause.

 

[Pat Hartman]

You can display the data however you want but as Galaxiom said, always use actual dates as your criteria. On your form, calculate the start date of the current quarter. Then the criteria would be >= Forms!myform!startQtr

 

[mib1019]

Your example doesn't fit your explanation:



This quarter begin on 10/1/2020, why are records of the first four dates not included? Why did you mention a specific day of the week in your example, but not mention it in your explanation?

You're right. It would include all records starting 9/28/20 and greater, as for company purposes, the whole week is part of Q4.

Thanks for catching that.

 

[mib1019]

Beware using your quarter calculation to select in any query. It is very important to understand the meaning of SARGable in queries.

If you convert the dates in the field to the quarter string then select on that result, your query will have to process every record in the table to make the selection and won't be able to use the index that should be on the field. You won't notice the performance penalty until you have a considerable number of records but it will steadily bog down as record numbers climb.

However it is done, your query should directly compare the date field with a calculated date range on the right hand side of the Where clause.

The text string is calculated on a form and recorded in a table. I can use dates for the query though.

 

[plog]

Can of worms opened:

It would include all records starting 9/28/20 and greater, as for company purposes, the whole week is part of Q4.

So does that mean 9/28/20 is in Q3 and Q4? Or is it solely in Q4 and your company has a very convoluted way of defining quarters? If the latter, when does Q4 2020 end? Christmas?

Please write an all inclusive explanation of how to define a quarter in your organization.

 

[ebs17]

What I'm looking for in a query is the criteria to filter out everything earlier than the current quarter, so the records would include this quarter and all future.

This means, rephrased into actionable logic: All records with a date greater than or equal to the first day of the current quarter.

Copy into a standard module:

Public Function FirstDayOfQuarter(Optional ByVal AnyDate As Variant) As Date
    Dim UsedDate As Date
    If IsMissing(AnyDate) Then
        UsedDate = Date
    Else
        UsedDate = AnyDate
    End If
    FirstDayOfQuarter = DateSerial(Year(UsedDate), ((Month(UsedDate) - 1) \ 3) * 3 + 1, 1)
End Function

Such a VBA function can be used directly in a query:

SELECT
   *
FROM
   TabX
WHERE
   MyDateField >= FirstDayOfQuarter()

 

[Pat Hartman]

I thought if the quarter started mid-week, the poster wanted to go back to the Monday, so the function needs to find the Monday date. Here's a database with some useful date functions.

 

[mib1019]

Can of worms opened:



So does that mean 9/28/20 is in Q3 and Q4? Or is it solely in Q4 and your company has a very convoluted way of defining quarters? If the latter, when does Q4 2020 end? Christmas?

Can of worms opened:



So does that mean 9/28/20 is in Q3 and Q4? Or is it solely in Q4 and your company has a very convoluted way of defining quarters? If the latter, when does Q4 2020 end? Christmas?

Please write an all inclusive explanation of how to define a quarter in your organization.

Please write an all inclusive explanation of how to define a quarter in your organization

Database is used for the purposes media buys, with the quarter starting on the Monday of the week where the quarter rolls. 2021-Q1 begins on 12/28. Air-times they pay for during the last few days of the month are billed in the new quarter. This database doesn't address what their books would use; I'm sure they go by 1st day of the month.

 

[mib1019]

This means, rephrased into actionable logic: All records with a date greater than or equal to the first day of the current quarter.

Copy into a standard module:

Public Function FirstDayOfQuarter(Optional ByVal AnyDate As Variant) As Date
    Dim UsedDate As Date
    If IsMissing(AnyDate) Then
        UsedDate = Date
    Else
        UsedDate = AnyDate
    End If
    FirstDayOfQuarter = DateSerial(Year(UsedDate), ((Month(UsedDate) - 1) \ 3) * 3 + 1, 1)
End Function

Such a VBA function can be used directly in a query:

SELECT
   *
FROM
   TabX
WHERE
   MyDateField >= FirstDayOfQuarter()

Thank you! That looks like it will work, and will test it tomorrow when I get back to my office.

 

[mib1019]

I thought if the quarter started mid-week, the poster wanted to go back to the Monday, so the function needs to find the Monday date. Here's a database with some useful date functions.

I have some date functions in my db but will add whatever you've given me here that are different. Thanks!

 

[plog]

What I'm looking for in a query is the criteria to filter out everything earlier than the current quarter, so the records would include this quarter and all future.

Working in quarters confuses the issue. Comparing dates is easy and built into Access; comparing essentially 2 different but related values (Year and Quarter) makes it harder because you will have to build something to do that comparison. So, ultimately you need a way to determine what the first date of the quarter a date falls in. Once you have that you can easily do comparisons. This is that function:

Public Function get_QuarterStart(in_Date) As Date
' determines first date of quarter in_Date occurs in

int_DaysToMonday = 7 - Weekday(in_Date, 2)
  ' number of days from in_Date to next monday

dt_Base = DateAdd("d", int_DaysToMonday, in_Date)
  ' date of Monday occuring after in_Date
 
ret = DateSerial(Year(dt_Base), ((Month(dt_Base) - 1) \ 3) * 3 + 1, 1)
  ' date of first day of actual quarter of dt_Base
 
int_DaysToMonday = (Weekday(ret, 3) Mod 7) * -1
  ' number of days from first day of quarter to prior monday

ret = DateAdd("d", int_DaysToMonday, ret)
  ' moves return value to Monday prior to first date of actual quarter
 
get_QuarterStart = ret

End Function

Paste it into a module then you can build a query and use it like this:

SELECT DateField, get_QuarterStart(Date_Field) AS DateField_QuarterStartDate, get_QuarterStart(Date()) AS Current_QuarterStartDate
FROM YourTableNameHere
WHERE DateField>=get_QuarterStartDate(Date())

Make it work with your database's YourTableNameHere table and its DateField date field and it will show you all records that have a DateField in and after the current quarter we are in.

 

[Galaxiom]

SELECT DateField, get_QuarterStart(Date_Field) AS DateField_QuarterStartDate, get_QuarterStart(Date()) AS Current_QuarterStartDate
FROM YourTableNameHere
WHERE DateField>=get_QuarterStartDate(Date())

Make it work with your database's YourTableNameHere table and its DateField date field and it will show you all records that have a DateField in and after the current quarter we are in.

Plog: Can you come up with away to do this with a SARGable query. (See Post #3)

 

[plog]

Isn' it? I think you've busted me on this before and I've tried to learn. According to wikipedia you don't want a field used in a function in the WHERE:

Sargable - Wikipedia

en.m.wikipedia.org

My query adheres to that but does use Date() within a function in the WHERE. You have a better list of rules/considerations i can refer to than wikipedia?

 

[Galaxiom]

Isn' it? I think you've busted me on this before and I've tried to learn. According to wikipedia you don't want a field used in a function in the WHERE:

Sargable - Wikipedia

en.m.wikipedia.org

My query adheres to that but does use Date() within a function in the WHERE. You have a better list of rules/considerations i can refer to than wikipedia?

Quite right. Apologies. My mistake from just glancing at the query with my eyes crossed.

 

[plog]

Looking at your post better I think putting the function in the SELECT is bad too.

However I did that merely for verification purposes, so that mib could verify my function was calculating the first date of each quarter correctly.

 

[Galaxiom]

Looking at your post better I think putting the function in the SELECT is bad too.

However I did that merely for verification purposes, so that mib could verify my function was calculating the first date of each quarter correctly.

It is extra overhead to calculate if you don't need it but as long as the WHERE can use the index it wouldn't really be a problem because on the desired records would be returned. Nice to include as verification during development but I would leave it out in production unless it had a purpose.

 

[mib1019]

Working in quarters confuses the issue. Comparing dates is easy and built into Access; comparing essentially 2 different but related values (Year and Quarter) makes it harder because you will have to build something to do that comparison. So, ultimately you need a way to determine what the first date of the quarter a date falls in. Once you have that you can easily do comparisons. This is that function:

Public Function get_QuarterStart(in_Date) As Date
' determines first date of quarter in_Date occurs in

int_DaysToMonday = 7 - Weekday(in_Date, 2)
  ' number of days from in_Date to next monday

dt_Base = DateAdd("d", int_DaysToMonday, in_Date)
  ' date of Monday occuring after in_Date

ret = DateSerial(Year(dt_Base), ((Month(dt_Base) - 1) \ 3) * 3 + 1, 1)
  ' date of first day of actual quarter of dt_Base

int_DaysToMonday = (Weekday(ret, 3) Mod 7) * -1
  ' number of days from first day of quarter to prior monday

ret = DateAdd("d", int_DaysToMonday, ret)
  ' moves return value to Monday prior to first date of actual quarter

get_QuarterStart = ret

End Function

Paste it into a module then you can build a query and use it like this:

SELECT DateField, get_QuarterStart(Date_Field) AS DateField_QuarterStartDate, get_QuarterStart(Date()) AS Current_QuarterStartDate
FROM YourTableNameHere
WHERE DateField>=get_QuarterStartDate(Date())

Make it work with your database's YourTableNameHere table and its DateField date field and it will show you all records that have a DateField in and after the current quarter we are in.

I'm good with not using the Quarter field for the query, just need all dates that fall into the current and future quarters, with the wrinkle of needing dates to include back to Monday of the week where the quarters start.

Thanks for your help!

 

[Pat Hartman]

Maybe I missed something but you don't need to use the function in a query. Put the function on a form and calculate the start date there. Then just do what I said earlier - SomeDate <= Forms!myform!calcDate