Selecting records 11 months prior to StartDate

Hi Experts

I have created a query with the following criteria and based on a table with the fields [StartDate] and [EndDate]…

Between DateAdd("m",-11,[StartDate]) And DateAdd("m",-1,[EndDate])

If I set the [StartDate] on 01/01/2014 and the [EndDate] on 31/01/2014, I get the correct records returned starting 01/02/2013 to the 31/12/2013 (11 months). However, if I set the [StartDate] to 01/02/2014 and [EndDate] to 28/02/2014 my query will only return those records from the 01/03/2014 to the 28/01/2014.

I would really appreciate some help on how to change the criteria to include records to the last day of the month for the [EndDate] (31/01/2014)? I've searched extensively and can't find anything that is helping.

Thanks
darls15
darls15Asked:
Who is Participating?
 
GozrehCommented:
is your EndDate always the last day of the month ?  
if yes you can use "Between DateSerial(Year([StartDate]), Month([StartDate])-11,Day([StartDate]) And DateSerial(Year([EndDate]),Month([EndDate]),1)-1"
0
 
Rey Obrero (Capricorn1)Commented:
try this criteria


Between dateserial(Year([StartDate]), Month([StartDate])-11,Day([StartDate]) And dateserial(Year([EndDate]),Month([EndDate])-1,Day([EndDate]))
0
 
darls15Author Commented:
@Rey, thank you, however your query returned the only the records to the 24/01/2014. also, i placed a closing bracket before the And statement to get it to work, hope this was correct.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
darls15Author Commented:
hi gozreh, i've tested your solution and it works as i need it to. i also needed to put a closing bracket before the And statement. thank you.
0
 
Rey Obrero (Capricorn1)Commented:
sorry, typo missing ")"

Between dateserial(Year([StartDate]), Month([StartDate])-11,Day([StartDate])) And dateserial(Year([EndDate]),Month([EndDate])-1,Day([EndDate]))


what are the startDate and EndDate that you selected?
0
 
GozrehCommented:
DateSerial(Year([EndDate]),Month([EndDate]),1)-1  this will always give the last date of the previous month.
0
 
Rey Obrero (Capricorn1)Commented:
to get the last date of the Previous month, the syntax is

DateSerial(Year([EndDate]),Month([EndDate]),0)
0
 
darls15Author Commented:
hi rey, i set the [StartDate] to 01/02/2014 and [EndDate] to 28/02/2014. i tried this again and it is still only returning records from 01/03/2013 to 28/01/2014.
0
 
Rey Obrero (Capricorn1)Commented:
try this


Between dateserial(Year([StartDate]), Month([StartDate])-11,Day([StartDate])) And DateSerial(Year([EndDate]),Month([EndDate]),0)
0
 
darls15Author Commented:
thank you both. i have given equal points to both your solutions as both will be helpful to me. your help is appreciated.
0
 
GozrehCommented:
For your example if you need 11 months prior to date, you don't need a start date and end date, you can use = Between DateSerial(Year(Date()),Month(Date())-11,1) And DateSerial(Year(Date()),Month(Date()),0)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.