Parameter Query to Find Records <= 3 yrs Prior to Entered Date

[Brent E]

Good day,

I am looking for the criteria statement for a parameter query to prompt the
user to enter a date MM/DD/YYYY format and then the query to locate any
records dated 3 yrs or more prior to the entered date.

For example, if a user entered 9/1/2008, the query would return all records
w/ a date prior to and including 9/1/2005

Pseudo criteria something like:
<= [Enter Date] -3 yrs

Thanks in advance,

 

[Douglas J. Steele]

<= DateAdd("yyyy", -3, [Enter Date])

 

[Roger Carlson]

Use the DateAdd function:

<= DateAdd("yyyy", -3, [Enter Date])

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Dirk Goldgar]

Good day,

I am looking for the criteria statement for a parameter query to prompt
the
user to enter a date MM/DD/YYYY format and then the query to locate any
records dated 3 yrs or more prior to the entered date.

For example, if a user entered 9/1/2008, the query would return all
records
w/ a date prior to and including 9/1/2005

Pseudo criteria something like:
<= [Enter Date] -3 yrs

Try:

<= DateAdd("yyyy", -3, [Enter Date])

I suggest you also use the Parameters dialog (menu item Query ->
Parameters..., while in the query's Design View) to define the [Enter Date]
parameter as Date/Time. It may not be required, but will ensure proper
interpretation of the parameter.

 

[Brent E]

Thanks All. That's great. Thanks Dirk too for the extra recommendation. That
will be useful.

Good day,

I am looking for the criteria statement for a parameter query to prompt
the
user to enter a date MM/DD/YYYY format and then the query to locate any
records dated 3 yrs or more prior to the entered date.

For example, if a user entered 9/1/2008, the query would return all
records
w/ a date prior to and including 9/1/2005

Pseudo criteria something like:
<= [Enter Date] -3 yrs

Try:

<= DateAdd("yyyy", -3, [Enter Date])

I suggest you also use the Parameters dialog (menu item Query ->
Parameters..., while in the query's Design View) to define the [Enter Date]
parameter as Date/Time. It may not be required, but will ensure proper
interpretation of the parameter.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)