Solved

Query 2 quarter data

Posted on 2014-02-05
8
315 Views
Last Modified: 2014-02-20
Hello,
I'm working with data that includes 6 months of results (counting the current month).  Using DateAdd I've been able to get the data to go back to the first of the month so it captures the entire data regardless of the current date.  Hopefully that makes sense.  So for example as of today it's pulling in 9/1/2013 - today.  So now I need to capture (from this data) 2 quarters, the current quarter and the previous quarter.  My logic is I want it to include the full three months of the previous quarter (so it would ignore the September data because I don't have the full quarter represented) and the current quarter no matter how much of it I have.  It's a running data sort of thing.  Can this be done?  I've tried to DateAdd using "q" but it's not liking my logic.  Any assistance would be great.  Hopefully you can understand what I'm after.  

Thanks in advance!

Sarah
0
Comment
Question by:SarahDaisy8
8 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39836509
You can use this equation ([Dt] is your date field):
DateDiff("m",[Dt],Date())-Month(Date()) Mod 3-3
if result is < 0, date is in expected range
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 200 total points
ID: 39836859
The following determines which quarter contains todays date, and then goes back 2 quarters:
select	DateAdd(quarter,-2,	case	when Month(current_timestamp) IN (1,2,3) then '01/01/'
					when Month(current_timestamp) IN (4,5,6) then '04/01/'
					when Month(current_timestamp) IN (7,8,9) then '07/01/'
					else '10/01/'
				end + cast(Year(current_timestamp) as varchar))

Open in new window

0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39838183
You can use the generic functions below to create a filter:

Where [YourDateField] Between DatePreviousQuarterFirst() And DateThisQuarterLast()

However, as you have no data later than the current date, you can use:

Where [YourDateField] Between DatePreviousQuarterFirst() And Date()

Public Function DatePreviousQuarterFirst( _
  Optional ByVal datDateThisQuarter As Date) As Date
  
  Const cintQuarterMonthCount   As Integer = 3
  
  Dim intThisMonth              As Integer
  
  If datDateThisQuarter = 0 Then
    datDateThisQuarter = Date
  End If
  intThisMonth = (DatePart("q", datDateThisQuarter) - 1) * cintQuarterMonthCount

  DatePreviousQuarterFirst = DateAdd("q", -1, DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 1))

End Function


Public Function DateThisQuarterLast( _
  Optional ByVal datDateThisQuarter As Date) As Date
  
  Const cintQuarterMonthCount   As Integer = 3
  
  Dim intThisMonth              As Integer
  
  If datDateThisQuarter = 0 Then
    datDateThisQuarter = Date
  End If
  intThisMonth = DatePart("q", datDateThisQuarter) * cintQuarterMonthCount
  
  DateThisQuarterLast = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 0)

End Function

Open in new window

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39840283
These are all great solutions, thank you!  Ace, with yours I was able to use it (not quite understanding what it was doing, but it works) but I put <4 instead and got exactly what I needed.  However, I'm not sure that will be sufficient for when my data moves into the start of October.  What do you think?  Thanks!
0
 
LVL 40

Expert Comment

by:als315
ID: 39841101
I can't agree with -4. You can test it. Look at sample
Database2.accdb
0
 
LVL 1

Author Comment

by:SarahDaisy8
ID: 39842271
Thanks Als315. I'll test it when I get home but I trust you heh. So is there no way of getting around using a function?  Though I suppose it's just called in the query, right?  Thanks again.

Sarah
0
 
LVL 40

Accepted Solution

by:
als315 earned 300 total points
ID: 39842577
Sorry, Sarah, my solution was wrong - it will not work for every 3-rd month. We can correct it:
(DateDiff("m",[Dt],Date())-3-Choose(Month(Date()) Mod 3+1,3,1,2))<0

You can use function and it is right way if you don't want to use web database. You can trust Gustav's solution. He is the best in date functions.

I can explain my idea:
1. Calculate month's number in current quarter:
Month(Date()) Mod 3
remainder after number is divided by 3
result will be 1 for January, April, 2 for February, May, but 0 for March and June
But we need values 1, 2, 3, so we can use Choose function for correct values: 1, 2, 3
Index in this function should start from 1, so we should add 1 to Mod's result:
Choose(Month(Date()) Mod 3+1,3,1,2)
You like to have all previous quarter, so you should add 3:
Now we can go back with DateDiff and get result
It can also be done with function:
Function check_date(Dt As Date) As Boolean
Dim M As Integer, N As Integer
M = Month(Date) Mod 3
check_date = False
Select Case M
    Case 0
        N = 6
    Case 1
        N = 4
    Case 2
        N = 5
End Select
If DateDiff("m", Dt, Date) - N < 0 Then check_date = True
End Function

Open in new window

Database2-1.accdb
0
 
LVL 1

Author Closing Comment

by:SarahDaisy8
ID: 39874031
Thank you so much for these solutions! I'm so sorry for the delay in reply.  Ace, I'm able to use your solution.  Thank you so much for your time!

-Sarah
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

726 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