I have a form set up to help users filter through different data records as they come through our workflow. On the form there is a check box (CurCycle in the query below) that if they select and then refresh the form it will just show them the proposals that have been entered into the database this year.
What I'd like is for in January and February for the data that's displayed to be for the current year or the past year. So basically, if it's January or February 2021, I want them to see data entered into the database during 2020 or 2021 (so the year for Date_Added would be 2020 or 2021). If it's January or February 2022, they should see data entered during 2021 or 2022. Then, once we move into March, they would just see the data for the current year. Is there a way to do this?
SELECT DISTINCT tbl_Main.Subject
FROM tbl_Main INNER JOIN tbl_CurriculumTypes ON tbl_Main.Proposal_Type = tbl_CurriculumTypes.Proposal_Type
WHERE (((Year([Date_Added]))=IIf([Forms]![ProposalSearch_MOV]![CurCycle]=True,Year(Now()),Year([Date_Added]))) AND ((tbl_CurriculumTypes.Curriculum_Type)="course") AND ((tbl_Main.MOV)=True) AND ((tbl_Main.EXCLUDE)=False));
Also, I noticed that when I added the check box to the form and incorporated it into the query, the form is a little bit slower when I click through it. Is there a more efficient way to set this up?
Thanks!