Solved

End of This Quarter from Today

Posted on 2014-12-10
7
143 Views
Last Modified: 2014-12-10
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
0
Comment
Question by:pdvsa
  • 3
  • 2
  • 2
7 Comments
 
LVL 51

Accepted Solution

by:
HainKurt earned 250 total points
ID: 40491698
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40491860
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40491901
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40491912
Sorry, meant the questioneer's library ...

/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 40491938
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40491959
Well, you asked for this quarter.
For the next quarter, again look up your own library and use:

    Me!txtDateTo = DateNextQuarterLast(Date)

/gustav
0
 

Author Comment

by:pdvsa
ID: 40492002
Yes, I am playing with you.  Thank you for the correction.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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