MS query help

[chally3]

Good afternoon all,

Is anyone familiar with Microsoft query via ODBC ? I am currently learning the ropes with this and have a query.
I have the extract below which pulls data out of our system from 01/01/15 onward, but I really wish to do is pull data from todays date -365 days (so I get a rolling years data every time I run the query) but I am struggling to do this.

Can anyone help with how I could rewrite the query below

WHERE PTMFILE.PTMPTNO = STHFILE.STHPTNO AND ((STHFILE.STHTYPE='A') AND (STHFILE.STHDATE>{d '2015-01-01'}) OR (STHFILE.STHTYPE='E') AND (STHFILE.STHDATE>{d '2015-01-01'}) OR (STHFILE.STHTYPE='G') AND (STHFILE.STHDATE>{d '2015-01-01'}) OR (STHFILE.STHTYPE='I') AND (STHFILE.STHDATE>{d '2015-01-01'}))

thank you,

Kind regards
Mark

 

[ChrisM]

The easiest way is just to do the date math directly in your sql statement, and that depends on the source database you are linking to (Access, Sql Server, etc)

If it's Access for example, you can use Now(), so your sql would be:

STHDATE >= (Now - 365)

Other databases uses different terms for today's date, SYSDATE, etc

If that doesn't work, you can set up parameters within MS Query:

In MS Query, in the criteria section, where you have '2015-01-01', replace that with a bracket and a dummy name, like [startdate]. Save and close the query, it will prompt you for a start date, just type any date.

Your results will be returned to worksheet. Now, right click anywhere within those results, and you will see a Parameters menu option. click it, and you can now point "startdate" to pull from a cell in your workbook. use an Excel formula to control the date in that cell.

 

[chally3]

Thank you Chris,

I'll give that a go, much appreciated.