Date functions in query: need to go back certain # of months

[Gooter]

I want the user to enter a date into a form (mm/yyyy) and then I want it to search the database up to 24 months prior to the date that was entered and return the results. So, user enters 6/2003 and it should return everything that has a date between 6/2001 and 6/2003.

Here is a basic formula for what I think it should do:
Date user enters = x

>=(x - 24 months) And <=(x)

And, I am thinking the query statement should look something like this?

>=(([Forms]![FormName]![UserDate]) - 24months) AND <=[Forms]![FormName]![UserDate]

I am obviously incorrect because I cannot get it to work. I do not know if this is possible or how to tell it to go back 24 months from the date the user provided.

Can anyone help?

Thanks!

 

[mpastore]

Use dateAdd()

For example

?dateAdd(&quot;yyyy&quot;,-2,date())

gives 6/10/2001

Your logic will be something like:

>=(dateAdd(&quot;yyyy&quot;,-2,[Forms]![FormName]![UserDate])) AND <=[Forms]![FormName]![UserDate]

Mike Pastore

Hats off to (Roy) Harper

 

[judgehopkins]

http://www.developershandbook.com/downloads/2162ch03.pdf

Download this file for info on dates.




Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.