I have a Query setup that shows Company Name, Participation Status, Program Begin Date, and Program End Date. The query is generating the program end date by adding 365 to the begin date which is fine.
My problem is when I run the query it is showing all records. I need to only display the records where the program end date is 90 days away from today and the status is enrolled.
I also need to do the same with 60 days away from today and I assume it will be the same as 90 days just changing the # of days but I can’t figure out how to write the query. Any help provided would be appreciated.
This query you have is showing you all entries and calculating an end date by adding 365 days to the start date. Can you do a second query that will take the results from the first (thereby applying your calculation), but set the criteria to only show you results in 90 days?
I assume you have a yes/no field for “enrolled”, so adding that to your query should control that part.
Okay… assuming that you’re using the traditional access query design view, you need to become familiar with the ‘criteria’ lines. These specify which records you want to see in the query. (Specifically, each criteria line represents a possible combination of values… multiple entries in the same criteria line require a AND b, whereas seperate criteria lines create an a OR b condition, but this is more info than you need to know to begin with.)
the ‘status is enrolled’ part should be pretty easy. First, make sure that there’s already an status column in the query design… if you’re using an asterisk (*) notation to ‘display all fields’ there might not be, but you should be able to add it in again, and if necessary, clear the ‘show’ checkbox for the new status column.
Now, under criteria for your status field, type in ‘enrolled’ or ‘e’ or whatever is used in the database to stand for enrolled. Try running the query at this point to see if it works like this.
Doing date arithmetic is harder. I’d suggest playing around with the datediff function - datediff(“d”, startdate, now()) will give the number of days between the startdate field and now. (assuming that startdate is in the past - if it is in the future the function will return negatives.) Using this in the criteria field you should be able to get what you want: datediff(“d”, startdate, now()) > 240 or something, I dunno.
Hope this helps put you on the right track a little.
I think that the following may apply to what you are trying to do. It relates to what RumMunkey said about a query of a query. You can use the design query view to make your date range query, and instead of using a table as the source for the second query, use your first query as your source.
I have a query that is supposed to show all emplopyers with annivesary dates 90 days from today when I run it, it comes up with entries that are older than 90 days than today.
I’m using
>=274 as the Criteria
What can I change it to so it shows expiring entries that expire anywhere from today to 90 days from now but no further than 90 days?