Query based on a relative date range

[elastreto]

Hello,

I am running a query on all employees hired between 9 and 12 months ago. To do this, I have defined the following static criteria:

Between #9/28/2006# And #12/28/2006# And Is Not Null

I would ideally like to change this so that instead of listing actual dates, I could list relative dates, ie: Between 12 months ago and 9 months ago

Is this possible?

[mlcampeau]

Hello,

I am running a query on all employees hired between 9 and 12 months ago. To do this, I have defined the following static criteria:

Between #9/28/2006# And #12/28/2006# And Is Not Null

I would ideally like to change this so that instead of listing actual dates, I could list relative dates, ie: Between 12 months ago and 9 months ago

Is this possible?

The DateDiff() function will help you out here. Something like this shouuld work:

Between (DateDiff("m", -12,Date()) AND DateDiff("m",-9,Date())) AND Is Not Null

[NeoPa]

I would use the DateAdd() function myself, although I'm sure Michelle's version would work equally well. EG :

SELECT *
FROM [MyTable]
WHERE [MyDate] Between DateAdd("m", -12, Date()) And DateAdd("m", -9, Date())