End of This Quarter from Today

Experts,

I have a filter form with txtDateFrom and txtDateTo

I need a formula for showing the txtDateTo as the end of the this quarter.  

Private Sub btnQtrEnd_Click()

    Me!txtDateFrom = Date
    Me!txtDateTo = ?????

end sub

ie:  today (txtDateFrom) = Dec 10, 2014
txtDateTo would need to = DEc 31, 2014

thank you
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
use this function

Function dhFirstDayInQuarter( _
 Optional dtmDate As Date = 0) As Date
    ' Returns the first day in the quarter specified
    ' by the date in dtmDate.
    Const dhcMonthsInQuarter As Integer = 3
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhFirstDayInQuarter = DateSerial( _
     Year(dtmDate), _
     Int((Month(dtmDate) - 1) / dhcMonthsInQuarter) * _
     dhcMonthsInQuarter + 1, _
     1)
End Function

Open in new window


then call it like

Me!txtDateTo = dhFirstDayInQuarter(Dateadd("q",2, DATE))-1

basically, we add 2 quarter to current date, find first day of that quarter, then find previous day...

not tested, but with some tweak, it should work...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Taken from your own(!) library:
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
HainKurtSr. System AnalystCommented:
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
Sorry, meant the questioneer's library ...

/gustav
0
pdvsaProject financeAuthor Commented:
I forgot that I have this function in my library.  Gustav can read my db even without seeing it!  Amazing!! ;)

I did have to adjust it a bit to get what I wanted.  

Private Sub btnNextQtr_Click()
       
      Me!txtdatefrom = Date
      Me!txtDateTo = DateThisQuarterLast(DateAdd("q", 1, Date))
     
End Sub
0
Gustav BrockCIOCommented:
Well, you asked for this quarter.
For the next quarter, again look up your own library and use:

    Me!txtDateTo = DateNextQuarterLast(Date)

/gustav
0
pdvsaProject financeAuthor Commented:
Yes, I am playing with you.  Thank you for the correction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.