Solved

End of This Quarter from Today

Posted on 2014-12-10
7
141 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

22 Experts available now in Live!

Get 1:1 Help Now