iif statement * subreport

[znibk]

Hello to all of you, in the past you have been helpful in clearning up my
problems, so I hope you can clear this one up for me.

I have been asked to base my total expense report for the month on only the
checks which hae cleared the bank. I ave used these IIF statements which
say=sum([cleared],-1,[ckamt],0)) as my control source
=sum([cleared]="yes",[ckamt],0)

The subreport is totaling the expenses, however, it is not leaving out the
checks which have not cleared the bank. To accomplish that task, I added the
field [Cleared] with data type set to yes/no in the table, queries, etc.
However, as I stated above, the subreport is still calculating the subreort
based on all checks written during the month for expenses.

Can somone help me come up with the correct IIF statement, and what I should
do?

Thanks a bunch and God Bless all of you for the help you give me (us).

 

[tkelley via AccessMonster.com]

May I ask why you don't just add the criteria of [Cleared] = -1 to your
report's underlying query? Why include the checks you don't want in the
underlying query, then add the overhead of trying to filter them out via IIf
functions?

I realize that may sound simple ... so I may have missed something in your
question. If so, please explain a little more.

Hello to all of you, in the past you have been helpful in clearning up my
problems, so I hope you can clear this one up for me.

I have been asked to base my total expense report for the month on only the
checks which hae cleared the bank. I ave used these IIF statements which
say=sum([cleared],-1,[ckamt],0)) as my control source
=sum([cleared]="yes",[ckamt],0)

The subreport is totaling the expenses, however, it is not leaving out the
checks which have not cleared the bank. To accomplish that task, I added the
field [Cleared] with data type set to yes/no in the table, queries, etc.
However, as I stated above, the subreport is still calculating the subreort
based on all checks written during the month for expenses.

Can somone help me come up with the correct IIF statement, and what I should
do?

Thanks a bunch and God Bless all of you for the help you give me (us).

 

[Duane Hookom]

It's difficult to understand if this is a report or subreport. If you want to
sum the ckamt values in a report's record source with Cleared = true, try:
=Sum(Abs(Cleared) * [CkAmt])
This assume Cleared is yes/no and CkAmt is numeric.

 

[znibk]

tkelley

Thank you so very much. I "knew that!" Sometimes I panic and when I do, I
forget everything I have ever known. Now all I have to do is go through and
correct all the mess I made.

Thank you again so much for reminding me and God Bless you.

May I ask why you don't just add the criteria of [Cleared] = -1 to your
report's underlying query? Why include the checks you don't want in the
underlying query, then add the overhead of trying to filter them out via IIf
functions?

I realize that may sound simple ... so I may have missed something in your
question. If so, please explain a little more.

Hello to all of you, in the past you have been helpful in clearning up my
problems, so I hope you can clear this one up for me.

I have been asked to base my total expense report for the month on only the
checks which hae cleared the bank. I ave used these IIF statements which
say=sum([cleared],-1,[ckamt],0)) as my control source
=sum([cleared]="yes",[ckamt],0)

The subreport is totaling the expenses, however, it is not leaving out the
checks which have not cleared the bank. To accomplish that task, I added the
field [Cleared] with data type set to yes/no in the table, queries, etc.
However, as I stated above, the subreport is still calculating the subreort
based on all checks written during the month for expenses.

Can somone help me come up with the correct IIF statement, and what I should
do?

Thanks a bunch and God Bless all of you for the help you give me (us).

 

[znibk]

Duane,

Yes, [Cleared] is yes/no and [CkAmt] is numeric and I thank you so much for
replying, but tkelly reminded me of something I completely had forgotten made
life much more simple and that is to add to the criteria in my query of
[Cleared] = -1

Thank you again for replying and God Bless you.
k

It's difficult to understand if this is a report or subreport. If you want to
sum the ckamt values in a report's record source with Cleared = true, try:
=Sum(Abs(Cleared) * [CkAmt])
This assume Cleared is yes/no and CkAmt is numeric.

--
Duane Hookom
Microsoft Access MVP

Hello to all of you, in the past you have been helpful in clearning up my
problems, so I hope you can clear this one up for me.

I have been asked to base my total expense report for the month on only the
checks which hae cleared the bank. I ave used these IIF statements which
say=sum([cleared],-1,[ckamt],0)) as my control source
=sum([cleared]="yes",[ckamt],0)

The subreport is totaling the expenses, however, it is not leaving out the
checks which have not cleared the bank. To accomplish that task, I added the
field [Cleared] with data type set to yes/no in the table, queries, etc.
However, as I stated above, the subreport is still calculating the subreort
based on all checks written during the month for expenses.

Can somone help me come up with the correct IIF statement, and what I should
do?

Thanks a bunch and God Bless all of you for the help you give me (us).

 

[tkelley via AccessMonster.com]

No prob. I like Duane's suggestion too. It would work very nicely if your
report were to total cleared checks, but also report outstanding checks.
Depending on what your customer wants of course ...

tkelley

Thank you so very much. I "knew that!" Sometimes I panic and when I do, I
forget everything I have ever known. Now all I have to do is go through and
correct all the mess I made.

Thank you again so much for reminding me and God Bless you.

May I ask why you don't just add the criteria of [Cleared] = -1 to your
report's underlying query? Why include the checks you don't want in the

[quoted text clipped - 22 lines]