Filter out current quarter's records from query (1 Viewer)

[gojets1721]

I have a query that looks at the last 2 years of records based on the 'SubmissionDate' field. Below is the SQL:

SELECT *
FROM qryAllComplaints
WHERE (((Year([SubmissionDate]))>=(Year(Date())-1)))
ORDER BY SubmissionDate;

I want to use this query as the source for a second one, but one where the current quarter's records are filtered out.

Any suggestions on how to code that?

 

[theDBguy]

Are we talking calendar or fiscal year? If fiscal, what is yours?

 

[gojets1721]

Calendar. So ideally, this query would filter out July 23, August 23, and Sept 23 records until it becomes Q4

 

[ebs17]

WHERE SubmissionDate < DateSerial(Year(Date()), DatePart("q",Date()) * 3 - 2, 1)

 

[gojets1721]

WHERE SubmissionDate < DateSerial(Year(Date()), DatePart("q",Date()) * 3 - 2, 1)

And this will continue to work even when it turns to Q4 (i.e. Q4 will then be removed and Q3 will be included?)

 

[theDBguy]

And this will continue to work even when it turns to Q4 (i.e. Q4 will then be removed and Q3 will be included?)

To test it out, just replace Date() with an actual date value (properly delimited, of course).

 

[ebs17]

Are you still thinking or are you already testing?

 

[gojets1721]

To test it out, just replace Date() with an actual date value (properly delimited, of course).

Didn't think of that! It worked! THanks @ebs17 and @theDBguy

I appreciate the help

 

[theDBguy]

Didn't think of that! It worked! THanks @ebs17 and @theDBguy

I appreciate the help

We're happy to assist. Good luck with your project.