I'm working on a project that lists weekly deposit dates.
I need to create a report that reflects the deposit dates
to a quarterly format. I'm having trouble with the
process of converting dates to quarters and then I also
need to reflect a Total Sum of each quarters deposits.
Here's some code that might help:
It's called like:
?BEquarter(Date(),-1)
12/31/2003
?BEquarter(Date(),0)
10/1/2003
'---------------------------------------------------------------------------
----
Option Compare Database
Option Explicit
Function EOMonth(Anydate)
'---------------------------------------------------------------------------
----
'Purpose: Returns the last day of the month for the date specified
'Accespts: A Date or Date Variable.
'Returns: VarType 7 Date
'---------------------------------------------------------------------------
---
On Error GoTo Err_EOM
Dim NextMonth, EndofMonth
NextMonth = DateAdd("m", 1, Anydate)
EndofMonth = NextMonth - DatePart("d", NextMonth)
EOMonth = EndofMonth
Exit_EOM:
Exit Function
Err_EOM:
MsgBox "Error" & " " & Err & " " & Error$
Resume Exit_EOM
End Function
Function BEQuarter(ByVal Anydate, BeginOrEnd As Integer) As Variant
'-------------------------------------------------------------------
'Purpose: Returns the beginning or the end of a quarter
'Uses: EOMonth() Function
'Input: AnyDate: A date value, use of #'s to signify a date when
variable
' from a query or the immediate window.
'' BeginOrEnd: 0 Finds Beginning of Quarter, -1 Finds End of
Quarter
''Returns: VarType 7 date
'---------------------------------------------------------------------
On Error GoTo Err_EOQ
If BeginOrEnd <> 0 And BeginOrEnd <> -1 Then
MsgBox "Error: BeginOrEnd must be 0 or -1"
GoTo Exit_EOQ
End If
Dim EndofQuarter, BeginofQuarter, PrevQuarter
Static MonthVar(12) As Integer
If MonthVar(12) = 0 Then
MonthVar(1) = 2
MonthVar(2) = 1
MonthVar(3) = 3
MonthVar(4) = 2
MonthVar(5) = 1
MonthVar(6) = 3
MonthVar(7) = 2
MonthVar(8) = 1
MonthVar(9) = 3
MonthVar(10) = 2
MonthVar(11) = 1
MonthVar(12) = 3
End If
Anydate = Anydate - DatePart("d", Anydate)
EndofQuarter = DateAdd("M", MonthVar(DatePart("M", Anydate)), Anydate)
EndofQuarter = EOMonth(EndofQuarter)
If DatePart("m", EndofQuarter) = 6 Then
BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 2
Else
BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 1
End If
If BeginOrEnd = -1 Then
BEQuarter = EndofQuarter
Else
BEQuarter = BeginofQuarter
End If
Exit_EOQ:
Exit Function
Err_EOQ:
MsgBox "Error" & " " & Err & " " & Error$
Resume Exit_EOQ
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access