Solved

Selecting records 11 months prior to StartDate

Posted on 2014-02-11
11
282 Views
Last Modified: 2014-02-11
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
0
Comment
Question by:darls15
  • 4
  • 4
  • 3
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this criteria


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

Accepted Solution

by:
Gozreh earned 250 total points
Comment Utility
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
 

Author Comment

by:darls15
Comment Utility
@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
 

Author Comment

by:darls15
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
DateSerial(Year([EndDate]),Month([EndDate]),1)-1  this will always give the last date of the previous month.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
to get the last date of the Previous month, the syntax is

DateSerial(Year([EndDate]),Month([EndDate]),0)
0
 

Author Comment

by:darls15
Comment Utility
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
try this


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

Author Closing Comment

by:darls15
Comment Utility
thank you both. i have given equal points to both your solutions as both will be helpful to me. your help is appreciated.
0
 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now