Criteria to pull a date range

If you want exactly a year from current date , use

DateSerial(Year(Date()) - 1,Month(Date()), Day(Date())) And DateSerial(Year(Date()),Month(Date()), Day(Date()))

Select allOpen in new window

It might help if you mentioned which db you're using.

 James

Wow, this kind of thing is way easier in DB2:

select someColumn
  from MyTable
 where someDate between current date - 1 year and current date

Select allOpen in new window

I appreciate all of the comments, but I believe that my question has been a little misinterpreted.

I am trying to pull a Rolling 12 Month date range meaning, for my report, I need it to show everything from the beginning of November 2010 until the end of October 2011.

I used your criteria, elimesika. Unfortunately, it returns the same results (earliest records have 06 January 2011). I know that the records are there for 2010 in the table being queried and when I used DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))  it pulled every record having 10/01/2010 in the date/time field.

Please help me to figure this out.

Isn't this just a simple as this, or am I missing something?

Between DateAdd("d",-365,Date()) And Date()

~bp

Sorry, that syntax gives me a Data Type Mismatch? I can provide a sample database if you would like?

can anyone help me with this please?

What's the data type of the field you are adding this condition to?

~bp

it is a True Date/Time field.

What SQL engine are you running on? (SQL Server, Oracle, MySQL, ...)

~bp

SQL Server

Then this should work:

Between DateAdd("d",-365,GetDate()) And GetDate()

~bp

Which version of SQL Server?

Also, when you say that you can "see" the data with earlier dates, exactly how are you "seeing" them?

Finally, what is the SQL statement you are using when you say "when I used DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))  it pulled every record having 10/01/2010 in the date/time field"

Billprew:  the GetDate() function you told me to put came back with an error message stating: "Undefined function 'GetDate' in expression".  

8080 Driver:  I used my old criteria (since I have been trying to make it pull the right data so much) and it doesn't pull any records now? So I am not sure what to do?

Hmmm, that's odd because I'm pretty sure that's been in SQL Server for a long time.  It certainly worked for me here...

Can you supply the surrounding context for this between statement, where is it benig used?

~bp

You said that you're using "SQL Server".  You did mean MS SQL Server, correct?  If so, which version?  It has had GetDate since MS SQL Server 2000, and I doubt they've removed that function from the latest version.  Also, you used DateSerial in your first post, which MS SQL Server didn't used to have.  Maybe they've added it, or maybe you have your own implementation.  If it's a function that was added to your system, then there's always the possibility that it's not working properly.

 James

Agreed, we need

(1) exact details on what SQL platform and version you are running

(2) more context on where you are using the BETWEEN clause, what's the surrounding code, what type of object is it in, etc

~bp

Based on the asker's other questions, it's the revolutionary, fool-proof, in-no-way-hackable "MS Access" DB engine ;)

Actually it accounted for the month as well using a Between....And Date Range. I figure it out.

I chose you because you wanted to help me.

Thanks.

~bp