Query Question (1 Viewer)

[jkabush]

I have a problem with one of my queries that I created and due to my lack of Access experience I'm not sure how to fix it. In my contract database, I have a renewal field and one calculated field called 90 days. In my 90days query, I used Month(Now()) in the criteria field and everything was working fine. However, once I started to enter in more contracts with different years, they would also appear in the query.

Example of when I run my query, I would get the following results.
Contract A 8/1/2015
Contract B 8/24/2015
Contract C 8/8/2017

How do I fix this problem so that when I run my query it will only show me current year dates?

Thanks for your help.

James

 

[robslob]

Try adding this clause

DatePart("yyyy",[ContractDate])=DatePart("yyyy",Date())

but will you need to show contracts that are for the next year if you run the query in later months?

 

[plog]

robslob gave you the answer you asked for, but I'm not certain its the answer you need. What happens when you run your query on 12/20/2015? What results do you expect?

You didn't really explain what you are trying to achieve, you just asked how to incorporate the current year into the query. If you would like to better explain your situation and explain what you are trying achieve, using no database jargon, we can help get you exactly what you need.

 

[jkabush]

This is how my current database is setup. I have a renewal field, 30Days, 60Days, 90Days calculated field ([renewal] -30, etc..), which are displaying the correct dates. What I am trying to achieve is to see what contracts expire in 30, 60, 90 days out.

Here is an example of one of my queries.

Field: Month([30 Days])
Criteria: Month(Now())

When I run the above query, it would show me everything due 30 days out. However, since I'm not using the year, it would show me contracts that are due in two years also.

Since they are wanting 30, 60, 90 days out when a contract expires, I would need to be able to pull information from next year (2016) also.

Hopefully this make it clearer of what I'm looking for.

James

 

[plog]

I suggest you create a query to calculate the exact number of days until expiration. If [renewal] is the date it needs to be renewed, you would create a query based on your table and use this to determine how many days exactly until renewal:

DaysToRenewal: DateDiff("d", Date(), [renewal])