Sorry Jeannie, I was busey Friday PM and than enjoyed the weekend with the
family. Here I think is the issue. What you need to do is in the two text
boxes, you need to recreate the formaly based on the underlying query/table
data source.
I assume the data source contains a field "payments" and a field named
"Charges".
Substitute your own names as necessary.
Now in an unbound field in your report you type:
=IIF(Sum([payments])-Sum([Charges])>0,Sum([payments])-Sum([Charges]),"")
This gives you the data for the Credit textbox.
Now in the second unbound textbox for the Debit type:
=IIF(Sum([payments])-Sum([Charges])<0,Sum([payments])-Sum([Charges]),"")
What I understand from your replies is that you have created an unbound
textbox on your report where you currently calculate the credit, Text99 for
the payments and Text98 for charges.
In report footers you can not always use previous created textboxes for this
report and just refer to their textbox name and create further calculations,
this is why I say to resourt to the underlying data source for your
information.
I hope I understand correctly. If you wish you may email me directly at
fonsponsio at provider sbcglobal.net.
Hope this helps.
Fons
Jeannie said:
No, sorry, here it is, the value in text100 is as follows:
=Val([Text98]-[Text99])
If this total is less than 0 is should go in credit and if it is more than 0
is should go in debit.
My problem with your statement is that I am prompted for a parameter value
when I try to run my report with the following statement:
=IIf(Sum([Text98])-Sum([Text99])>0,Sum([Text98])-Sum([Text99]),"")
Thanks
Jeannie
Fons Ponsioen said:
This is because the way (sequence) how the report is processed. There are
others who can be more specific, I am like you, I have been working with
Access a long time and do read a lot and learn very much from posting on this
site.
Did it work out the way I told you?
Fons
Jeannie said:
Hi Fons,
I put this for credit
=IIf(Sum([Text98])-Sum([Text99])>0,Sum([Text98])-Sum([Text99]),"")
Text98 represents the text box containing the total GST and Text99 contains
the total QST
When I do this, I get prompted for a parameter value for text98 and one for
text99
Any ideas why my report is not recognizing my statement?
Thanks again
Jeannie
:
Jeannie.
Let us say that the texbox containing the difference contains:
=Sum([payments])-Sum([Charges])
you can place in the unbound texboxes for the Debit and Credit:
for the Credit Textbox:
=IIF(Sum([payments])-Sum([Charges])>0,Sum([payments])-Sum([Charges]),"")
and for the Debit Textbox:
=IIF(Sum([payments])-Sum([Charges])<0,Sum([payments])-Sum([Charges]),"")
This should work.
Fons
:
I have a report and in the report footer, I show the difference between the
billed expenses and the expenditures. Now, I added text boxes that have to
do the following:
If the total in the difference is less than 0 it has to appear in the debit
text box and if the difference is more than 0 it has to appear in the credit
text box. I have tried various iff statements for these text boxes, without
success. Whenever I enter the name of the text box containing the total in
my statement (ex: text100 which contains the formula
=Val([Text98]-[Text99])), it prompts me for that text box when I try running
the report, I tried putting the formula, but got the same result.
Can anyone help me with this
Thanks
Jeanne
