Solved

End of This Quarter from Today

Posted on 2014-12-10
7
142 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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