Using Year(Now()) To Display Current and Past Year Depending on Current Month

[oleander]

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!

[Micron]

I want them to see data entered into the database during 2020 or 2021

I don't see how you could have or want an OR situation for this - or do you mean AND? Why not just allow the choice of which year? You could probably use a ready-built calendar form for this. Perhaps you don't want anyone looking at January 2022 records until March, but that seems odd.

[oleander]

Sorry, I meant 'and'. So in January or February, they would see the data for both 2020 and 2021, and then moving into March they would just see data for 2021. That's just kind of how our cycle works.

[Micron]

I have to go out but will check back later. Not sure if you want to go back 2 months into the prior year or right back to the beginning of the prior year. I suspect you mean the latter.

[oleander]

Ended up going a different route. Instead of a check box I'm just having a text box where the user can input a year and show the data for that year.

[Micron]

Did you format the textbox as a date so that you get a calendar, or decide not to have that?