First Add the following function to a module in the database:
Public Function AccountingYear(varDate As Variant, _
intStartMonth As Integer, _
intStartDay As Integer) As String
Dim intYear As Integer
If Not IsNull(varDate) Then
intYear = Year(varDate)
If intStartMonth = 1 And intStartDay = 1 Then
' accounting year is calendar year
AccountingYear = intYear
Else
' if date is before start of accounting year then
' accounting year starts in previous year
If varDate < DateSerial(intYear, intStartMonth, intStartDay) Then
AccountingYear = intYear - 1 & "-" & intYear
Else
' date is after start of accounting year so
' accounting year starts in current year
AccountingYear = intYear & "-" & intYear + 1
End If
End If
End If
End Function
This returns the accounting year for any date by passing the date and the
month and day on which the accounting year starts into the function. Then
use a query along these lines:
SELECT TransactionDate, TransactionAmount, TransactionDistrictID,
(SELECT SUM(TransactionAmount)
FROM Transactions AS T2
WHERE T2.TransactionDistrictID = T1.TransactionDistrictID
AND ACCOUNTINGYEAR(T2.TransactionDate,7,1)
= ACCOUNTINGYEAR(T1.TransactionDate,7,1)
AND DATEPART("q",T2.TransactionDate)
<> DATEPART("q",T1.TransactionDate)
AND T2.TransactionDate< T1.TransactionDate)
AS PreviousBalance
FROM Transactions AS T1
WHERE YEAR(TransactionDate) = [Enter year:] AND
DATEPART("q",TransactionDate) = [Enter quarter:]
ORDER BY TransactionDistrictID, TransactionDate;
This will prompt for the year and quarter. Note that the year and quarter
entered should be the calendar year and quarter, not your accounting year and
quarter, e.g. for you sample data from 7/1/09 to 9/30/09 you'd enter 2009 and
3 at the prompts.
The way it works is that the subquery sums the rows where the transaction
district is the same and the transaction dates are (a) within the same
accounting year, (b) not within the same quarter and (c) earlier than the
outer query's date.
Ken Sheridan
Stafford, England
Hi Benjamins,
I tried the function but the Expr1 field comes up with a blank value. Is
there something I'm doing wrong? This is what I get on the query for the
transactions between 7/1/09 and 9/30/09.
TransactionDate TransactionAmount TransactionDistrictID Expr1
7/1/09 500 El monte city
8/15/09 500 El monte city
9/21/09 500 El monte city
But I need to get this
TransactionDate TransactionAmount TransactionDistrictID Expr1
7/1/09 500 El monte city
300
8/15/09 500 El monte city
300
9/21/09 500 El monte city
300
[quoted text clipped - 29 lines]
quarter ending balance). For the statement I send out for the second quarter
(10/1/09 - 12/31/09) my previous balance should be $1,800.00
--
Message posted via AccessMonster.com
.
