Criteria to filter down to YTD across multiple years?

[templeowls]

So I've got a query that is full of customer complaints from 2018 up to today. I'm interested to see if there's a way to code criteria that filters down to nothing beyond today's date across all previous years. So that essentially it only shows records for 1.1.2018 - 6.24.2018, 1.1.2019 - 6.24.2019, 1.1.2020 - 6.24.2020, and 1.1.2021 - present....if that makes sense.



My query is fairly simple but I can attach an example if needed. The fields are 'submissiondate' 'customername' and 'category'

[Micron]

I presume that today's date is not fixed and that tomorrow it will be 06/25?
You could use a combination of Date() as the current date and DateSerial(Year(Date()),1,1) or it might be DateSerial(Year(Date),1,1). Your query could use the Between operator as well: Between DATE() And DateSerial(Year(Date()),1,1)
I've never really learned why it's sometimes Date() and other time, just Date.

[Welshgasman]

I would build the criteria for each year using Microns solution and DateAdd() subtracting years from date() until 2018 or whenever?

[ssanfu]

I'm not sure if you want complaints between 1/1 and the current date or you want complaints for just 6 months. (1 - 6 or 7 - 12)

Given the data set below

submissiondate
1/4/2018
5/22/2020
1/4/2020
11/5/2020
3/20/2019
4/26/2018
7/18/2021
6/22/2021
6/29/2021
6/29/2019

(10 records)

the query

SELECT tblComplaints.Category, tblComplaints.customername, tblComplaints.submissiondate
FROM tblComplaints
WHERE (((tblComplaints.submissiondate)<=Date()) AND ((Month([submissiondate]))>=1 And (Month([submissiondate]))<=Month(Date())));

returns these dates

submissiondate
1/4/2018
5/22/2020
1/4/2020
3/20/2019
4/26/2018
6/22/2021
6/29/2019

(7 records - should be 6 records)

where the current date is 6/24/2021



I haven't figured out how to not include the date #6/29/2019#.

[templeowls]

Hi Steve, I used your code and worked, but similar to you, I'm having entries from 2019 and 2020 show up to the 30th rather than the 25th (today).

It's a small enough glitch where its usable still, but its weird nonetheless lol

[Welshgasman]

Include the day check with the month check?

[ssanfu]

@Welshgasman,
I tried that (if I did it right), but I looked to me that dates that should have been included weren't.

So that essentially it only shows records for 1.1.2018 - 6.24.2018, 1.1.2019 - 6.24.2019, 1.1.2020 - 6.24.2020, and 1.1.2021 - present....if that makes sense.

There wasn't an expected result set posted, so not exactly sure what fits.


If today is 6/24/2021, shouldn't 4/26/2018 be included in result set?

[Welshgasman]

Not easy I admit. All the brackets throw me as well.
I think I'd have to build the criteria in a function and perhaps use DateValue()

[CJ_London]

create a table to store your year starts

tblYears
Yr
1/1/2018
1/1/2019
etc


then your query

SELECT Yr, Count(submissionDate) as NumComplaints
FROM tblComplaints, tblYears
WHERE submissionDate between Yr and dateadd("yyyy",year(Yr)-year(Date()),Date())
GROUP BY Yr

add additional grouping as required

[CJ_London]

I haven't figured out how to not include the date #6/29/2019#

you need to include a criteria for day as well as year/month