Share via

Help for How to Create an Upcoming Due Dates List

Anonymous
2015-04-16T17:05:42+00:00

I'm pretty new to Access, so I apologize if this is a dumb question. I'm creating a contracts management database to be able to search contracts and display a dashboard that lists upcoming due dates and other event dates. I'm having trouble with creating the dashboard to display the dates.

I have a query (qryUpcomingEventDates) that calculates and contains all dates based on a table that has the "StartDate" and "RecurNumber". The query looks like this:

EventID InstanceID EventDate ContractID Comment RecurCount PeriodFrequency PeriodType ReminderType
1 1 4/30/2015 12345 Auto renews unless cancelled by 5/30/2015 10 1 year(s) REVIEW CONTRACT
2 1 5/2/2015 23456 Payment of $500 100 1 month(s) PAYMENT DUE
3 1 5/5/2015 34567 Must terminate by otherwise autorenews 5 1 year(s) TERMINATE BY

For this example, the contract would have to be reviewed on 4/30/205 every year for 10 years, so there is a date in this query on 4/30/2016, 4/30/2017, etc.

I want to make a dashboard on the frmHomePage (which is a Navigation Form) that displays the due dates ("EventDate" field in the query) that range from the current date through the next 45 days so that due dates from today out to 5/31/2015 pop up on the list.

What is the best way to do this? If the answer is "run another query off this one" then how will it re-run itself and be correct every time someone uses the database? Will it be able to be placed in a Navigation Form (the "Home Page")?

Any help is appreciated!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-04-22T19:44:12+00:00

    Thank you for your help! I ended up doing a variation of both replies. I couldn't figure out where and how to correctly enter the "WHERE" statement without an error, so I ran a query that calculated DaysUntilDue calculated by: [EventDate]-Date() and put the criteria as >0 And <=45 

    Then I put that query in a Datasheet Form and put it on my Navigation Page ("Home Page"). I'm not sure if this will update each day, though? Do I need to add something else to make it update every day? Or do I need to open the query itself in order for it to update?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-04-16T23:27:08+00:00

    Try this --

    WHERE (((Int(DateSerial(Year(Date()),Month([EventDate]),Day([EventDate])))) Between Int(Date()) And Int(Date())+45))

    If you have any nulls it will give an error.

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2015-04-16T19:43:08+00:00

    Hi Scott! Thanks for the response! So I already have a query that calculates the event dates, that's what qryUpcomingEventDates does. What I meant by the "4/30/2016, 4/30/2017, etc" is that because I entered that the "due date is on 4/30/2015" and I need to be reminded to review the contract every year on 4/30 for the next 10 years, in qryUpcomingEventDates all of these records will appear in the query:

    EventID InstanceID EventDate ContractID Comment RecurCount PeriodFrequency PeriodType ReminderType
    1 1 4/30/2015 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 2 4/30/2016 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 3 4/30/2017 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 4 4/30/2018 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 5 4/30/2019 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 6 4/30/2020 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 7 4/30/2021 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 8 4/30/2022 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 9 4/30/2023 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT
    1 10 4/30/2024 12345 Auto renews unless cancelled by 5/30 10 1 year(s) REVIEW CONTRACT

    I guess my question then (since you mentioned filtering) is whether I am able to apply a permanent filter to the query to only display dates between today (current date) and 5/31/2015 (45 days from now)?

    Was this answer helpful?

    0 comments
  5. ScottGem 68,820 Reputation points Volunteer Moderator
    2015-04-16T17:36:14+00:00

    Is your query calculating the next Due Date? That's unclear. Also unclear is what you mean by "so there is a date in this query on 4/30/2016, 4/30/2017"?

    The bottomline is that you will need a query that calculates the next event date and is filtered for that date less 45 days. So I would add another column:

    CheckDate: DateAdd("d",-45,[EventDate])

    Was this answer helpful?

    0 comments