By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,062 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,062 IT Pros & Developers. It's quick & easy.

Report sums based on record dates

P: n/a
I have a table of transactions, some with past dates, some dated the
current date, and some dated in the near future. On a report, I'm
looking for a way to get four different sums using the transactions in
the table, each sum based on the dates of the transactions. I need a
sum of transactions in the current month dated the 15 though the 31st,
the sum of transactions from next month dated 1st through 14th, the sum
of transactions next month dated 15th through 31st (or 30th depending
on the month) and the sum of transactions from the month after next
dated the 1st through the 14th.
I think I've got one working. This one seems to work for next month
dated 15th through 31st:
=Sum(IIf([DateTransaction]>=DateSerial(Year(Date()),Month(Date())+1,15-0),[AmountDebit],0))

But I can' figure the others out. Thanks for your help.

Aug 20 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"rinmanb70" <em**********@gmail.comwrote in
news:11*********************@74g2000cwt.googlegrou ps.com:
I have a table of transactions, some with past dates, some
dated the current date, and some dated in the near future. On
a report, I'm looking for a way to get four different sums
using the transactions in the table, each sum based on the
dates of the transactions. I need a sum of transactions in the
current month dated the 15 though the 31st, the sum of
transactions from next month dated 1st through 14th, the sum
of transactions next month dated 15th through 31st (or 30th
depending on the month) and the sum of transactions from the
month after next dated the 1st through the 14th.
I think I've got one working. This one seems to work for next
month dated 15th through 31st:
=Sum(IIf([DateTransaction]>=DateSerial(Year(Date()),
Month(Date())+1,15-0),[AmountDebit],0))

But I can' figure the others out. Thanks for your help.
Use the divide and conquer technique.

In the query for your report, put some calculated fields.
you want year(dateTransaction), month(datetransaction) and
iif(day(dateTransaction<15,1,2). Name them a, b and c Now you
group the report on a,b,c and sort the report on
datetransaction.

Use a group footer for the c group with the expression
sum(amountdebit).

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 20 '06 #2

P: n/a
That looks it would be a great solution. It would allow me to group
the transactions on the report apart from each other based on their
trans date. I would like to do that if you could tell me a couple of
things about how to do it. I'm new so I apologize for asking ignorant
questions here. I don't understand
What it means to put calculated fields into a query
What this does (if(day(dateTransaction<15,1,2)
How to name them a, b and c
I think I can figure out how to group on a,b, and c once I see them.

Thanks...

Aug 20 '06 #3

P: n/a
comments in line.

"rinmanb70" <em**********@gmail.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
That looks it would be a great solution. It would allow me to
group the transactions on the report apart from each other
based on their trans date. I would like to do that if you
could tell me a couple of things about how to do it. I'm new
so I apologize for asking ignorant questions here. I don't
understand What it means to put calculated fields into a query
open the query builder tool. choose your table, put the fields
from the tables in the bottom section by double-clicking the
name in the upper panel of the query builder.
Now to add the calculated fields, type
a: year([datetransaction]) in the next column that doesn't
already have a field name in it. Then type
b: month([datetransaction]) in the next column that doesn't
already have a field name in it. and finally type
c: IIF(day([datetransaction]< 15,1,2) in another column.
What this does (if(day(dateTransaction<15,1,2)
should be IIF(....
This makes each day in month if less than 15 equal 1 and all
other days in month equal 2

31 july 2006 = a:2006 b:7 c:2
13 august 2006 = a:2006 b:8 c:1
14 august 2006 = a:2006 b:8 c:1
15 august 2006 = a:2006 b:8 c:2
13 september 2006 = a 2006 b 9 c 1
14 september 2006 = a 2006 b 9 c 1
15 september 2006 = a 2006 b 9 c 2
30 september 2006 = a 2006 b 9 c 2

How to name them a, b and c
see above.
I think I can figure out how to group on a,b, and c once I see
them.

Thanks...



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 20 '06 #4

P: n/a
I copied and pasted the text you suggested from your instructions and I
believe I did as you said, but after I pasted the term c:
IIF(day([datetransaction]< 15,1,2) into the last column, when I try to
move to another field it gives this error:
The expression you entered has a function containing the wrong number
of arguments.

Can you tell what the problem would be?

Aug 21 '06 #5

P: n/a
As I was closing the query I went ahead and removed the C: column that
was giving the error so that it would let me save the query, but then
after that and after I name it, it gives the error:

Extra ) in query expression 'tblTransaction.[Year(]datetransaction])]'.

(It actually does show the 2nd close bracket at the end of the
expression on the error screen, but I double checked and it is not on
the expression in the column.)

Aug 21 '06 #6

P: n/a
"rinmanb70" <em**********@gmail.comwrote in
news:11**********************@74g2000cwt.googlegro ups.com:
As I was closing the query I went ahead and removed the C:
column that was giving the error so that it would let me save
the query, but then after that and after I name it, it gives
the error:

Extra ) in query expression
'tblTransaction.[Year(]datetransaction])]'.

(It actually does show the 2nd close bracket at the end of the
expression on the error screen, but I double checked and it is
not on the expression in the column.)

typo on my part ] before datetransaction should be [

tblTransaction.Year([datetransaction])
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 21 '06 #7

P: n/a
"rinmanb70" <em**********@gmail.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
I copied and pasted the text you suggested from your
instructions and I believe I did as you said, but after I
pasted the term c: IIF(day([datetransaction]< 15,1,2) into the
last column, when I try to move to another field it gives this
error: The expression you entered has a function containing
the wrong number of arguments.

Can you tell what the problem would be?
typographical error, missing a ) at the end.
IIF(day([datetransaction]< 15,1,2))

( must ALWAYS have )
and [ must always have ]

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 21 '06 #8

P: n/a
First I should say the the a: and b: colummns saved successfully.
(Thank you)

On the c: column, I've been pasting directly from the webpage or
pasting to notepad (to make sure I'm not putting in extra spaces or
characters) then pasting from notepad to the query, but after I paste
the term:
c: IIF(day([datetransaction]< 15,1,2))
into the last column, when I try to
move to another field it gives this error:

The expression you entered has a function containing the wrong number
of arguments.

Aug 22 '06 #9

P: n/a

rinmanb70 wrote:
First I should say the the a: and b: colummns saved successfully.
(Thank you)

On the c: column, I've been pasting directly from the webpage or
pasting to notepad (to make sure I'm not putting in extra spaces or
characters) then pasting from notepad to the query, but after I paste
the term:
c: IIF(day([datetransaction]< 15,1,2))
into the last column, when I try to
move to another field it gives this error:

The expression you entered has a function containing the wrong number
of arguments.
Take 5 minutes to read the help files on day() and on IIF().
then try to paste this.
c: IIF(day([datetransaction])< 15,1,2)

Aug 22 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.