Hello,
I have created a database for a Board of Directors - names, photos, contact details, training, etc etc. Each Director is on the Board for a term, let's say 4 years. Technically the Board year is from fiscal year to fiscal year but not every person enters / leaves the Board on the fiscal year start / end date. So I am capturing when the person joined the Board (YYYY-MM-DD, this is the From Date) and then the To Date (YYYY-MM-DD), when the term is to end. This database has current data and historic.
I want to create a report of the Board of Directors for a particular year. I don't want to create a report per year. I want to create a generic report, where the client will input the Board year they want to report on, then a query will go pick up the particular rows.
If the client wants a report for the 2013-2014 year, I'd like an input field for the From Date and an input field for the To Date. Then I want this to go to the Board table data and pick up all those records that are within that year. So e.g., Susie might be on Board from 2012-2016. She'll be in the report. Bob is in there from 2009-2013, so he doesn't show up. John from 2014-2018 doesn't show up. etc.
I have the input pop-ups in the Query right now against the Board table, but can't figure out the criteria statement to pick up the correct rows. Maybe i need a between clause, but that seems to be for 'get me these rows between this date and this date' - but i have 2 dates in my table + the 2 inputs….
Also, i'd like the client to just put in the fiscal year (YYYY) as an input, but my data is in YYYY-MM-DD. I know I can use parts of a date but i don't want to store this date (the input part of the date) on a form or table anywhere, although i suppose it could be 'stored' in the title of the report itself.
Oh, finally, i was hoping to do this in a Query or SQL, rather than VB code which i'm not as familiar with.
Thanks so much!!