SarahDaisy8
asked on
Query 2 quarter data
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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()
Where [YourDateField] Between DatePreviousQuarterFirst()
However, as you have no data later than the current date, you can use:
Where [YourDateField] Between DatePreviousQuarterFirst()
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
/gustav
ASKER
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!
I can't agree with -4. You can test it. Look at sample
Database2.accdb
Database2.accdb
ASKER
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
Sarah
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
-Sarah
DateDiff("m",[Dt],Date())-
if result is < 0, date is in expected range