Finding the date a month before

[shadow]

Hi all, i have table with a renewal date such as 10/10/2009. I need a query which will return all the policys that are 10/09/2009.. so basically a month before they happen.

I just need to know the code to go into the search Criteria. Thanks

 

[Brianwarnock]

use Dateadd to subtract a month

Brian

 

[shadow]

I just want something to go in here where is says "need code for here"

 

[Alansidman]

Put a new column in your query and add a DateAdd Expression. Make your current field for the Policy Date to not visible.

Here is the syntax for DateAdd

http://www.techonthenet.com/access/functions/date/dateadd.php

Alan

 

[Brianwarnock]

Have you asked the correct question? I think you want to know all policies that are going to be renewed in one month, therefore in that criteria you need =dateadd("m",1,date()) or maybe even<=

But you should read about dateadd and not expect others to do the actual work.

Brian

 

[datAdrenaline]

With all due respect to Alasidman, I do not beleive that it is appropriate in this case to add a column as you have described ...

The criteria of the field indicated should be placed exactly where has been indicated.

the DateAdd() function can be will be useful but {Click Here for a list of all functions ... they are in the Help files too} It seems you wish to find all policies that will need to be renewed in the next month, so you will need an upper and lower date boundary, the upper being today and the lower being one month from today. You can use the Between..And sql construct for that.

As Brian indicated, you should read up to learn how to use the function, plus the excercise in discovering the information on DateAdd() may help you learn how to discover more functions and such ... ...

------

So ... as an example of finding a date value that is between today and one month in the future, you can use something like this ...

Between Date() And DateAdd("m", 1, Date())

----

Oh ... Click Here for one of my primary locations for help ...