Calculate Start and End Date of a Quarter

[Dani2009]

I issue quarterly statements to customers. In order to show on the statement
the previous balance as of the last quarter, I need to sum up all the
transactions that took place before the start of the new quarter. How can I
do this in query? For example:
Our fiscal year starts on July 1, 2009. The customer has the following
transactions:

5/29/2009 100.00
6/1/2009 200.00
7/1/09 500.00
8/15/09 500.00
9/30/09 500.00

For the statement I send out for the first quarter (from 7/1/09 to 9/30/09)
I want to sum the transactions before 7/1/09 which will be $300.00 (previous
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

 

[KARL DEWEY]

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

 

[Dani2009]

I am sorry, I'm not sure if I got what you mean. I put the funtion and I get
-1 and 0.
I don't know where to put the funtion you provided.

This is the function I had before, but it was pulling the transactions as of
8/30/09.

DSUM("TransActionAmount","Transactions","TransactionDate<=DateSerial(Year(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

I need a function that will help me pull the sum of the transactions taking
into consideration the date range I select, for example if I want to see the
what transactions the district had from 7/1/09 to 9/30/09 I need to be able
to see the following in a report:


Previous Balance $ 300.00
DistrictID TransactionDate TransactionAmount
100 7/1/09 $500.00
100 8/15/09 $500.00
100 9/30/09 $500.00
Ending
Balance $1,800.00

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.

I issue quarterly statements to customers. In order to show on the statement
the previous balance as of the last quarter, I need to sum up all the
transactions that took place before the start of the new quarter. How can I
do this in query? For example:
Our fiscal year starts on July 1, 2009. The customer has the following
transactions:

5/29/2009 100.00
6/1/2009 200.00
7/1/09 500.00
8/15/09 500.00
9/30/09 500.00

For the statement I send out for the first quarter (from 7/1/09 to 9/30/09)
I want to sum the transactions before 7/1/09 which will be $300.00 (previous
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

 

[KARL DEWEY]

What I posted was criteria for your date field to be used in your query.

--
Build a little, test a little.

I am sorry, I'm not sure if I got what you mean. I put the funtion and I get
-1 and 0.
I don't know where to put the funtion you provided.

This is the function I had before, but it was pulling the transactions as of
8/30/09.

DSUM("TransActionAmount","Transactions","TransactionDate<=DateSerial(Year(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

I need a function that will help me pull the sum of the transactions taking
into consideration the date range I select, for example if I want to see the
what transactions the district had from 7/1/09 to 9/30/09 I need to be able
to see the following in a report:


Previous Balance $ 300.00
DistrictID TransactionDate TransactionAmount
100 7/1/09 $500.00
100 8/15/09 $500.00
100 9/30/09 $500.00
Ending
Balance $1,800.00

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.

I issue quarterly statements to customers. In order to show on the statement
the previous balance as of the last quarter, I need to sum up all the
transactions that took place before the start of the new quarter. How can I
do this in query? For example:
Our fiscal year starts on July 1, 2009. The customer has the following
transactions:

5/29/2009 100.00
6/1/2009 200.00
7/1/09 500.00
8/15/09 500.00
9/30/09 500.00

For the statement I send out for the first quarter (from 7/1/09 to 9/30/09)
I want to sum the transactions before 7/1/09 which will be $300.00 (previous
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

 

[KARL DEWEY]

-----ERROR------
What I posted was SQL 'WHERE' statement. To use it in design view this way
---
Expr1: Format([transactionDate], "yyyy") & Format([transactionDate],
"q")

Criteria below the above calculated field --
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.

I am sorry, I'm not sure if I got what you mean. I put the funtion and I get
-1 and 0.
I don't know where to put the funtion you provided.

This is the function I had before, but it was pulling the transactions as of
8/30/09.

DSUM("TransActionAmount","Transactions","TransactionDate<=DateSerial(Year(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

I need a function that will help me pull the sum of the transactions taking
into consideration the date range I select, for example if I want to see the
what transactions the district had from 7/1/09 to 9/30/09 I need to be able
to see the following in a report:


Previous Balance $ 300.00
DistrictID TransactionDate TransactionAmount
100 7/1/09 $500.00
100 8/15/09 $500.00
100 9/30/09 $500.00
Ending
Balance $1,800.00

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.

I issue quarterly statements to customers. In order to show on the statement
the previous balance as of the last quarter, I need to sum up all the
transactions that took place before the start of the new quarter. How can I
do this in query? For example:
Our fiscal year starts on July 1, 2009. The customer has the following
transactions:

5/29/2009 100.00
6/1/2009 200.00
7/1/09 500.00
8/15/09 500.00
9/30/09 500.00

For the statement I send out for the first quarter (from 7/1/09 to 9/30/09)
I want to sum the transactions before 7/1/09 which will be $300.00 (previous
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

 

[Dani2009]

I'm sorry again. I think now my subject doesn't really refers to the function
I need in my query.

I need to complement my DSUM function to pull the sum of the transactions
that are 1 day less than the criteria I specify from my Statement Form

What would be the complement of the DSUM function that would reference what
I enter as my start date on my Statement form?
DSUM("TransActionAmount","Transactions","TransactionDate<"
" AND TransactionDistrictID =" & [TransactionDistrictID])


TransactionDate Criteria
Between [Forms]![Statement]![StartDate] And [Forms]![Statement]![EndDate]

-----ERROR------
What I posted was SQL 'WHERE' statement. To use it in design view this way
---
Expr1: Format([transactionDate], "yyyy") & Format([transactionDate],
"q")

Criteria below the above calculated field --
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.

I am sorry, I'm not sure if I got what you mean. I put the funtion and I get
-1 and 0.
I don't know where to put the funtion you provided.

This is the function I had before, but it was pulling the transactions as of
8/30/09.

DSUM("TransActionAmount","Transactions","TransactionDate<=DateSerial(Year(Date()),((Month(Date())-1)\3)
* 3,0) AND TransactionDistrictID =" & [TransactionDistrictID])

I need a function that will help me pull the sum of the transactions taking
into consideration the date range I select, for example if I want to see the
what transactions the district had from 7/1/09 to 9/30/09 I need to be able
to see the following in a report:


Previous Balance $ 300.00
DistrictID TransactionDate TransactionAmount
100 7/1/09 $500.00
100 8/15/09 $500.00
100 9/30/09 $500.00
Ending
Balance $1,800.00

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

--
Build a little, test a little.


:

I issue quarterly statements to customers. In order to show on the statement
the previous balance as of the last quarter, I need to sum up all the
transactions that took place before the start of the new quarter. How can I
do this in query? For example:
Our fiscal year starts on July 1, 2009. The customer has the following
transactions:

5/29/2009 100.00
6/1/2009 200.00
7/1/09 500.00
8/15/09 500.00
9/30/09 500.00

For the statement I send out for the first quarter (from 7/1/09 to 9/30/09)
I want to sum the transactions before 7/1/09 which will be $300.00 (previous
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

 

[Benjamins via AccessMonster.com]

Hi Dani2009,

For Previous Balance
DSUM("TransActionAmount","Transactions","TransactionDate<" & [Forms]!
[Statement]![StartDate] & " AND TransactionDistrictID =" &
[TransactionDistrictID])

For Balance
DSUM("TransActionAmount","Transactions","TransactionDate<" & [Forms]!
[Statement]![EndDate] + 1 & " AND TransactionDistrictID =" &
[TransactionDistrictID])

I not sure is this what you want.

I'm sorry again. I think now my subject doesn't really refers to the function
I need in my query.

I need to complement my DSUM function to pull the sum of the transactions
that are 1 day less than the criteria I specify from my Statement Form

What would be the complement of the DSUM function that would reference what
I enter as my start date on my Statement form?
DSUM("TransActionAmount","Transactions","TransactionDate<"
" AND TransactionDistrictID =" & [TransactionDistrictID])

TransactionDate Criteria
Between [Forms]![Statement]![StartDate] And [Forms]![Statement]![EndDate]

-----ERROR------
What I posted was SQL 'WHERE' statement. To use it in design view this way

[quoted text clipped - 53 lines]

 

[Dani2009]

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

Hi Dani2009,

For Previous Balance
DSUM("TransActionAmount","Transactions","TransactionDate<" & [Forms]!
[Statement]![StartDate] & " AND TransactionDistrictID =" &
[TransactionDistrictID])

For Balance
DSUM("TransActionAmount","Transactions","TransactionDate<" & [Forms]!
[Statement]![EndDate] + 1 & " AND TransactionDistrictID =" &
[TransactionDistrictID])

I not sure is this what you want.

I'm sorry again. I think now my subject doesn't really refers to the function
I need in my query.

I need to complement my DSUM function to pull the sum of the transactions
that are 1 day less than the criteria I specify from my Statement Form

What would be the complement of the DSUM function that would reference what
I enter as my start date on my Statement form?
DSUM("TransActionAmount","Transactions","TransactionDate<"
" AND TransactionDistrictID =" & [TransactionDistrictID])

TransactionDate Criteria
Between [Forms]![Statement]![StartDate] And [Forms]![Statement]![EndDate]

-----ERROR------
What I posted was SQL 'WHERE' statement. To use it in design view this way

[quoted text clipped - 53 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

 

[KenSheridan via AccessMonster.com]

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

Hi Dani2009,

[quoted text clipped - 29 lines]

 

[Marshall Barton]

You asked for 'last quarter' and then 'first quarter'.
Here is last quarter --
WHERE Format([transactions], "yyyy") & Format([transactions], "q") =
Format(DateAdd("q", -1,Date()), "yyyy") & Format(DateAdd("q", -1,Date()), "q")

Nothing wrong with that, but it can be done using just:

WHERE Format([transactions], "yyyyq") = Format(DateAdd("q",
-1,Date()), "yyyyq")

I'm not sure what the OP wants so I can't say how applicable
it is to the question.

 

[Dani2009]

Hi KenSheridan,

I tried the function and the query, but the query keeps the previous balance
field keeps showing blank.
How can I modify the accountingyear module to start the fiscal year on July
1st instead of January 1st?

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

Hi Dani2009,

[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

--



.

 

[KenSheridan via AccessMonster.com]

Two points:

1. Accounting Year is not a module, it’s a function. If you've also named
the module in which you've placed it AccountingYear then rename the module to
something else, e.g. mdlAccountingStuff.

2. You don't need to make any modifications to the function. It will return
the accounting year starting at any date in the year by passing the month and
day of the month on which the accounting year starts into the function.
That's why its called with:

ACCOUNTINGYEAR(T2.TransactionDate,7,1)

and:

ACCOUNTINGYEAR(T1.TransactionDate,7,1)

in the query I posted. This returns the accounting year starting 1 July for
whatever data is entered as the first argument.

I have tested the query against a table of my own with near identical columns
to yours and it appears to work faultlessly. The PreviousBalance would only
be Null if there were no transactions for a customer in previous quarters of
the accounting year in question (including if the first quarter of the
accounting year is selected of course).

Don't forget the parameter values must be entered at runtime as the year and
quarter for the *calendar* year, though if you are using references to
controls on a dialogue form as the parameters it would be a simple task to
enter the year and quarter for your accounting year, e.g. 1 for July – Sept,
2 for Oct – Dec etc, and translate this in code into the year and quarter for
the calendar year, assigning the resulting values to hidden text boxes on the
form, which would be referenced as the parameters rather than the visible
text boxes.

Ken Sheridan
Stafford, England

Hi KenSheridan,

I tried the function and the query, but the query keeps the previous balance
field keeps showing blank.
How can I modify the accountingyear module to start the fiscal year on July
1st instead of January 1st?

First Add the following function to a module in the database:

[quoted text clipped - 82 lines]