Solved

End of This Quarter from Today

Posted on 2014-12-10
136 Views
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
Question by:pdvsa
• 3
• 2
• 2

LVL 51

Accepted Solution

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
``````

then call it like

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

Gustav Brock earned 250 total points
ID: 40491860
``````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
0

LVL 51

Expert Comment

ID: 40491901
0

LVL 49

Expert Comment

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

/gustav
0

Author Closing Comment

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

End Sub
0

LVL 49

Expert Comment

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

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

Featured Post

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…