Show data older than a specified number of years from future date

[randle]

Hi,

I've found plenty of help in regards to querying a field for a number of days from today's date but not had a lot of luck finding how to query this from a date in the future.

Currently I have as criteria for the relevant field:

<[Period Start (dd/mm/yy - less 5 years from financial year end)]

which prompts for a parameter but using this I have to figure out the date 5 years from the future date first and then enter this.

Surely there must be code that allows me to simply enter the date, in the prompt and this then shows me data of 5 years or older equipment!?

 

[pbaldy]

Check out the DateAdd() function.

 

[randle]

Thanks for the reply and can see that this would be what I'm after but how do I get it to prompt for the date?

<DateAdd("yyyy", -5, #01/05/2015#)

would obviously show me data before 01/05/2010 but I want this value to be entered at query run which is why I want the prompt.

 

[Brianwarnock]

<Dateadd("yyyy",-5,[Enter Date])

If enter date is not a field in your database, and if it is it should not have spaces in it , then Access will treat it as a parameter and prompt for its value.

Brian

 

[randle]

Thanks Brian,

I can't believe I didn't try this as is obvious now. Working a treat

 

[Brianwarnock]

I've seen a proverb somewhere that basically says, " the more we know the more we overlook the obvious"
I found it so true in my IT career.

Brian

 

[namliam]

How about this one:
The more we know, the more we know there is so much more to learn/know and ultimately forget

I have forgotten more about Pascal programming than I care to remember

 

[Brianwarnock]

I heard it differently
"The more we know, the more we know how little we know"

The point I was trying to make was that there is a time on the learning curve when we think that errors or things that we can't do must be very technical, which is why we cannot find are syntax errors for example.

Anyway I don't want to prolong this guys thread.

Cheers

Brian

 

[randle]

Very true.