CODEWITHSUNDEEP
ms-access
user2119980
user2119980

Reputation: 509

Pulling prior year data

I am working on a query to pull all turnover in the past calendar year that is going to be used daily. Rather than going in and having to change the date each time I would just like to be able to run the query and have it automatically only pull the last 365 days worth of data. The code itself looks like:

SELECT O867IA_VJOBHST.SYS_EMP_ID_NR, O867IA_VJOBHST.REC_EFF_STT_DT, O867IA_VJOBHST.EMP_ACN_TYP_CD
FROM O867IA_VJOBHST
WHERE (((O867IA_VJOBHST.EMP_ACN_TYP_CD)="HIR"));

Where the REC_EFF_STT_DT is the date the ACN_TYP_CD occurred, in this case when they were HIR (Hired)

Any Ideas?

Upvotes: 1

Views: 227

Answers (1)

HansUp
HansUp

Reputation: 97131

Access SQL provides Date() and DateAdd() functions. You can work out what you need from those functions in the Immediate window ...

? Date()
9/9/2013 
? DateAdd("d", -365, Date())
9/9/2012

Then you can filter REC_EFF_STT_DT on the same date range in a query like this ...

SELECT o.SYS_EMP_ID_NR, o.REC_EFF_STT_DT, o.EMP_ACN_TYP_CD
FROM O867IA_VJOBHST AS o
WHERE
    o.REC_EFF_STT_DT BETWEEN DateAdd('d', -365, Date()) AND Date();

Upvotes: 1

Related Questions