Report Total

[Tina]

Hi,

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt

Thanks..

 

[Marshall Barton]

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt

Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

 

[Tina]

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt

Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +

IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

 

[Tina]

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt

Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +

IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]

thanks..

 

[Marshall Barton]

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt

Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +

IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]

The multple revisions will have to be dealt with by
totalling them in the subreport. I would need more details
to be sure, but maybe you can use a total text box in the
subreport's footer section. This subreort footer section is
the one that the main report needs to refer to. Please
explain more about this, especially since will probably
impact what we do on the main report.

To deal with the situation where the subreport has no
revisions, change the reference to the revision total to use
the subreport's HasData property:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

But, this may well be the wrong expression if the subreport
arrangement needs to be changed.

 

[Tina]

Hi Marshall,

Yes, the revisions amounts are listed in my subreport. The subreport
displays the revision amount per group. I display the revision_amt,the date
and operator, whether the amount should be added or subtracted. I have this
information listed in the footer section of the group on my main report cause
I only want this information displayed once. The reason I need these totals
is because It affects my overall totals whether or not any revisions where
made to the group.
The subreport group is called tbl_Budget_revision

Let me know what other details you would need. thanks..

Tina wrote:
I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt


:
Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]

The multple revisions will have to be dealt with by
totalling them in the subreport. I would need more details
to be sure, but maybe you can use a total text box in the
subreport's footer section. This subreort footer section is
the one that the main report needs to refer to. Please
explain more about this, especially since will probably
impact what we do on the main report.

To deal with the situation where the subreport has no
revisions, change the reference to the revision total to use
the subreport's HasData property:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

But, this may well be the wrong expression if the subreport
arrangement needs to be changed.

 

[Marshall Barton]

I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt


:
Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt


:
Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]

The multple revisions will have to be dealt with by
totalling them in the subreport. I would need more details
to be sure, but maybe you can use a total text box in the
subreport's footer section. This subreort footer section is
the one that the main report needs to refer to. Please
explain more about this, especially since will probably
impact what we do on the main report.

To deal with the situation where the subreport has no
revisions, change the reference to the revision total to use
the subreport's HasData property:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

But, this may well be the wrong expression if the subreport
arrangement needs to be changed.

Yes, the revisions amounts are listed in my subreport. The subreport
displays the revision amount per group. I display the revision_amt,the date
and operator, whether the amount should be added or subtracted. I have this
information listed in the footer section of the group on my main report cause
I only want this information displayed once. The reason I need these totals
is because It affects my overall totals whether or not any revisions where
made to the group.
The subreport group is called tbl_Budget_revision

OK, maybe I understand(?) now.

First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Tina]

Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]

thanks!

Tina wrote:
I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt


:
Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt


:
Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Tina wrote:
I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]

The multple revisions will have to be dealt with by
totalling them in the subreport. I would need more details
to be sure, but maybe you can use a total text box in the
subreport's footer section. This subreort footer section is
the one that the main report needs to refer to. Please
explain more about this, especially since will probably
impact what we do on the main report.

To deal with the situation where the subreport has no
revisions, change the reference to the revision total to use
the subreport's HasData property:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

But, this may well be the wrong expression if the subreport
arrangement needs to be changed.

Yes, the revisions amounts are listed in my subreport. The subreport
displays the revision amount per group. I display the revision_amt,the date
and operator, whether the amount should be added or subtracted. I have this
information listed in the footer section of the group on my main report cause
I only want this information displayed once. The reason I need these totals
is because It affects my overall totals whether or not any revisions where
made to the group.
The subreport group is called tbl_Budget_revision

OK, maybe I understand(?) now.

First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Tina]

I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

Any ideas..

Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]

thanks!

Tina wrote:
I have built a report that also contains a sub-report.
I am building totals for my report and I have run into something I'm not
quite sure of how to do.

In the subreport it lists an amount field called revision_amt and another
field that displays whether the amt is an Add or Subtract, revision_operator.

Then in the report I have created a total text box where I take some amounts
from the report and add them together.
=[Planned Budget]-Sum([Invoice)]

What I would like to do is add on to this statement either adding or
subracting the revision_amt field from the sub-report. It should do a check
and if the amount is an add then it should add it, if subract then subract it
from =[Planned Budget]-Sum([Invoice)].

I assume It would be some sort of if statement. I tried this and it works
but will always add it and I want it to depend on the operation. =[Planned
Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt


:
Try this kind of thing:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt


:
Thanks Marsh. This works great if the operator is add. The operator can
only be add or subtract. How would I incorporate subtract into this if
statement? So if Subtract is the operator I want to subtract revision amt
instead of add it.
Would it be something like this?

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt

Tina wrote:
I know see that it gives the operator a 1 if it is add and a -1 if its a
subract. It still doesn't work cause I have multiple revisions and it seems
to be only processing the first one. Also if there is no revisions I am
getting an #Error. If there is no revisions I still want to do the following
=[Planned Budget] - Sum([Invoice)]


:
The multple revisions will have to be dealt with by
totalling them in the subreport. I would need more details
to be sure, but maybe you can use a total text box in the
subreport's footer section. This subreort footer section is
the one that the main report needs to refer to. Please
explain more about this, especially since will probably
impact what we do on the main report.

To deal with the situation where the subreport has no
revisions, change the reference to the revision total to use
the subreport's HasData property:

=[Planned Budget] - Sum([Invoice)] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

But, this may well be the wrong expression if the subreport
arrangement needs to be changed.

Yes, the revisions amounts are listed in my subreport. The subreport
displays the revision amount per group. I display the revision_amt,the date
and operator, whether the amount should be added or subtracted. I have this
information listed in the footer section of the group on my main report cause
I only want this information displayed once. The reason I need these totals
is because It affects my overall totals whether or not any revisions where
made to the group.
The subreport group is called tbl_Budget_revision

OK, maybe I understand(?) now.

First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Marshall Barton]

Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)
--
Marsh
MVP [MS Access]

I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]

First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Tina]

Hi Marshall,

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.

Any ideas?

Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)
--
Marsh
MVP [MS Access]

I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Marshall Barton]

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.

Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)

I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Tina]

Hi,

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.

Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Tina]

Hi, forget about this. I'm just going to do some other calculation.

Instead could you help me again with the following:

=[Planned Budget] - Sum([Invoice)] -[dsum] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

I have added a txt field that performs a calculation [dsum]. The statement
above works great but when there is no amt contained in [dsum] it causes
nothing to output for the statement above. Is there a way to output the
statement above without any data in [dsum]?

Hi,

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Marshall Barton]

It looks like you're using a _ instead of a . here. Also
the [tbl_GL_Account_GL_ID] reference need to refer to the
value in the report, not to a table the DSum never heard of.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
Forms![Report Date Range]![Beginning Trans Date] AND
GL_ID = Forms![Report Date Range]!GL_ID]")

or maybe that last line should be:
GL_ID = Reports![nameofreport]!GL_ID]")
--
Marsh
MVP [MS Access]

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

 

[Marshall Barton]

Whoops, I missed this message earlier.

No can do. The aggregate functions don't know anything
about forms/reports, much less about their properties or
controls.
--
Marsh
MVP [MS Access]

Hi, forget about this. I'm just going to do some other calculation.

Instead could you help me again with the following:

=[Planned Budget] - Sum([Invoice)] -[dsum] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

I have added a txt field that performs a calculation [dsum]. The statement
above works great but when there is no amt contained in [dsum] it causes
nothing to output for the statement above. Is there a way to output the
statement above without any data in [dsum]?

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] <
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are < the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)