# Access - Creating a query to check dates



## LiamCarson (Apr 2, 2010)

Hi there,

I am currently building a database in Access that has tables, forms, queries and reports to manage contracts. I have a field in my Contracts table that shows the date that each contract expires. 

I need to build a query that automatically checks all the contract expiry dates when the database opens, and tells the user which ones have an expiry date less than one month away. 

Note: I am using both Office 2007 and Office 2010 Beta for this project.
Any help greatly appreciated :smile:

Liam


----------



## RSpecianjr (Jan 20, 2010)

Hey LiamCarson,

You can create the query you want, then add this criteria to your Expiration date field:


```
DateDiff("d",Date(),[Field1])<=31 And DateDiff("d",Date(),[Field1])>=0
```
This will limit the data being shown by the contracts that expire between the current day and 31 days from then. For it to work we need to replace Field1 with your field name.

That should do it, but if you want the query to automatically run when you open the database, we have to create a macro for it. Here are the steps:

1. Click Create.
2. Click Macro.
3. Set Action to OpenQuery.
4. At the bottom you will have multiple things you need to fill out. First is the Query's name, second is the View you want to display, and finally the datamode... ie add edit or read only.
5. Save the macro as AutoExec.

Whenever you open your database, the macro should run your query for you.

Let me know if there are any problems or if you have any questions,

Hope this Helps,

Robert Specian Jr.


----------



## LiamCarson (Apr 2, 2010)

Hello Robert,
Thank you for your help, the query is now up and running! 

However the problem I have is some contracts are up to 5 years in length, but the query thinks they are going to expire even though they have serveral years left, as the day and month are within the 31 day bracket.

e.g. 03/05/2013 (dd/mm/yy) is showing up in the query results.

Is there some way to incorporate this into the code so that it recognises that the expiry date is not this current year?

Thanks again
Liam


----------



## JimBoCol (Apr 5, 2010)

LiamCarson said:


> Hello Robert,
> Thank you for your help, the query is now up and running!
> 
> However the problem I have is some contracts are up to 5 years in length, but the query thinks they are going to expire even though they have serveral years left, as the day and month are within the 31 day bracket.
> ...


I suggest that you change the Date field format to DD/MM/YYYY. That should do the trick for you.

Jim.


----------



## RSpecianjr (Jan 20, 2010)

Hey Liam,

Jim's suggestion should work perfectly. You could also use:


```
DateDiff("d",Date(),[Field1])<=31 And DateDiff("d",Date(),[Field1])>=0 And DateDiff("yyyy",Date(),[Field1])=0
```
Warm Regards,

Robert Specian Jr.


----------



## LiamCarson (Apr 2, 2010)

Thank you both for your quick and helpful replies, the database is now working perfectly.

Thanks again,
Liam


----------

