I am working on a database that I would like to use a dialog box to select criteria for a query that a report is based on. The report is data for an annual report...a list of names & other information on residents rotating for the year. Since this is for a University, the year begins on 7/1 of each year, and the rotations in the table/query the report is based on all have a start date associated with them. The data in the database is normalized.
The best way I could think of to do this was to generate a table for the dialog box with the various administrative information for each year for the next 20 years. I would imagine that they will be using something MUCH better by 2025. The data fields include:
a. The year as the ID (2005, 2006, etc.)
b. The report header they always use...(AY 2005-2006, etc.)
c. The start date of each year (7/1/2005, etc.)
d. The end date of each year (6/30/2006, etc.)
How do I get the combo box on the dialog form (for example "AY 2005-2006", which is what users see to select from) to always default to
1. The record for which today's date is between the dates of fields (c) and (d) above.
2. The record for which (today's date + 120 days) is between the dates of fields (c) and (d) above.
(I plan to create 2 similar dialog boxes for different purposes)
I am not stuck with this method since I only created the table to exist behind the dialog box to make it easier to print the data a particular way on the report AND make it easier for users to select the correct time frame for the report. I am open to alternate suggestions. Thanks for any assistance you can offer.
The best way I could think of to do this was to generate a table for the dialog box with the various administrative information for each year for the next 20 years. I would imagine that they will be using something MUCH better by 2025. The data fields include:
a. The year as the ID (2005, 2006, etc.)
b. The report header they always use...(AY 2005-2006, etc.)
c. The start date of each year (7/1/2005, etc.)
d. The end date of each year (6/30/2006, etc.)
How do I get the combo box on the dialog form (for example "AY 2005-2006", which is what users see to select from) to always default to
1. The record for which today's date is between the dates of fields (c) and (d) above.
2. The record for which (today's date + 120 days) is between the dates of fields (c) and (d) above.
(I plan to create 2 similar dialog boxes for different purposes)
I am not stuck with this method since I only created the table to exist behind the dialog box to make it easier to print the data a particular way on the report AND make it easier for users to select the correct time frame for the report. I am open to alternate suggestions. Thanks for any assistance you can offer.
