Help! Date Criteria: Between Last 12 Months

[toilettenseife]

Hello Access Community,



I am trying to create a criteria that returns records in the date range of last month and the 11 months before it.

Here is my attempt: Between DateSerial(year(DateAdd("y",-1,Date())),Month(Date()),1) and DateSerial(year(Date()),month(DateAdd("m",-1,Date())),30)

The two sections work independently. However, when I tie them together with "Between" and "And," it doesn't work.

Is there a better way to get records in this date range?

Thanks!

[Perceptus]

I dont see why you should have to convert the Date to Serial in order to work this.

[toilettenseife]

So, something like this? Between Month(Date())/1/year(DateAdd("y",-1,Date())) and Month(DateAdd("m",-1,Date()))/30/year(Date())

I got an error: inconsistent datatypes: expected Date got BINARY_DOUBLE

[Perceptus]

Between DateAdd("yyyy",-1,Date()) And DateAdd("m",-1,Date())

[toilettenseife]

Between DateAdd("y",-1,Date()) And DateAdd("m",-1,Date()) doesn't return any records. Between #11/1/2014# And #10/31/2015# returns 3,546 records so I know there are records there.

[toilettenseife]

Wait......

[toilettenseife]

I think it worked......

[toilettenseife]

Well, not exactly but I think I'm on the right track now. Thanks!

[Perceptus]

I think you neeed to use "yyyy" instead of "y"
http://www.techonthenet.com/access/f...te/dateadd.php

[toilettenseife]

I started to get returns after I changed it to "yyyy." However, Between DateAdd("yyyy",-1,Date()) And DateAdd("m",-1,Date()) gives me a date range equivalent to Between Date()-365 And Date I need whole months. Last month + the previous 11. Example: If I ran it today I would get the equivalent of Between #11/1/2014# And #10/31/2015#

[Perceptus]

Oh, well in that case you will probaly need to use DateSerial. im such a doofus.

[Perceptus]

Try

Between DateSerial(Year(Date())-1,Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0)

[toilettenseife]

Sir you are a genius and a gentleman. It works! Thanks!

[toilettenseife]

Now, how do you mark this post as solved?