Here it are the sums from the category footer:
[CategoryTotal] is =Sum(Val([InvoiceAmount]))
[CategorySubTotal] is =Sum([InvoiceAmount]*[Percentage])
[CategoryGST] is =[CategorySubTotal]*0.07
[CategoryQST] is =([CategorySubTotal]+[CategoryGST])*0.075
[CategoryGrandTotal] is [CategorySubTotal]+[CategoryGST]+[CategoryQST]
Then, I have the following sums in the Report Footer, for the Billing
category, I have:
[BillingSum] is =Sum(Abs([Category]="Billing")*[InvoiceAmount])
[BillingPercentage] is Percentage
[BillingSubTotal] is =[BillingSum]*[BillingPercentage]
[BillingGST] is =Val([BillingSubTotal]*0.07)
[BillingQST] is =Val(([BillingSubTotal]+[BillingGST])*0.075)
[BillingTotal] is =Val([BillingGST]-[BillingQST])
For the Expenditures and Personal expenses categories, I have the following
sums:
[ExpensesSum] is =Sum(Abs([Category]="Expenditures" Or [Category]="Personal
Expenses")*[InvoiceAmount]*[Percentage])
[ExpensesPercentage] is Percentage
[ExpensesSubTotal] is =[ExpensesSum]*[ExpensesPercentage]
[ExpensesGST] is =Val([ExpensesSubTotal]*0.07)
[ExpensesQST] is =Val(([ExpensesSubTotal]+[ExpensesGST])*0.075)
[ExpensesTotal] is =Val([ExpensesGST]-[ExpensesQST])
I then have the following totals:
[DifferenceSubTotal] is =[BillingSubTotal]-[ExpensesSubTotal]
[DifferenceGST] is =[BillingGST]-[ExpensesGST]
[DifferenceQST] is =[BillingQST]-[ExpensesQST]
[DifferenceTotal] is =[BillingTotal]-[ExpensesTotal]
It looks like this:
GST QST
Total
Billing $129.50 $148.46
-$18.96
Expenditures and expenses $50.58 $57.99 -$ 7.41
Difference between billing and payments $78.92 $90.47 -$11.56
Please note that [DifferenceSubTotal] is not visible.
Now, after all this, if the total is a minus I need it to appear in the
credit text box, I am trying to use the if statements I wrote earlier, but to
no avail.
Thanks again for your help
Jeannie
Fons Ponsioen said:
Jeannie. Okay, Now,
a. would you provide me the formulas for the sums you refer to in 2. in
your reply below.
b. IIf([BillingTotal] = < 0,"Val",[BillingTotal]), this IIF indicates that
if [BillingTotal] is less than or equal to 0 (zero), you want to print "Val"
in the textbox and if not, you want to print [BillingTotal]. Would you
provide me the formula you have in the "BillingTotal" Textbox.
Than we'll get to the next step.
Fons
:
1. you're right
2. [BillingTotal], ([BillingGST], and[BillingQST] are text boxes that I
renamed, these boxes were created to put formulas for sums
3. the statement IIf([BillingTotal] = < 0,"Val",[BillingTotal]) gives me a
syntax error message, but I should get -18.96
Thanks again for your help
Jeannie
:
Jeannie,
1. In the original message you wrote that the result of your messages was
Blank
Your IIF Statement:
=IIf(Val([BillingGST]-[BillingQST])<0,Val([BillingGST]-[BillingQST]),"")
indictes that if the the condition is not met, you want the result to be ""
(which is blank).
2. As I expressed in my original reply: Are [BillingTotal], ([BillingGST],
and[BillingQST]) in the data source? (in a query or table).
3. What is the result of your statement: =IIf([BillingTotal] = <
0,"Val",[BillingTotal])?
If I understand the answers to the above questions we can work on the
remainder of the issues.
Fons
:
My report is separated in many sections.
The report header = a title
The page header = titles for each column that appear
The detail section = non visible, because we only want to see the sums, not
each number
The category footer which contains the invoice total, gst, qst and total for
each category (billing, expenditures and personal expenses)
The page footer = the usual one with the date and page number
The report footer = contains formulas calculating the difference between
billing and expenditures. I also need to add control boxes which will show
when the total difference is a minus (credit) or a plus (debit).
I tried with the following formulas:
=IIf([BillingTotal] = < 0,"Val",[BillingTotal])
=IIf(Val([BillingGST]-[BillingQST])<0,Val([BillingGST]-[BillingQST]),"")
Any ideas?
Thanks for your help
Jeannie
:
Are [BillingTotal], ([BillingGST], and[BillingQST]) values from an underlying
table or query? Or are these the names of textboxes in the report? If they
are the textbox names from the report, what are the formulas and the actual
names of the data field from the underlying table(s)/query.
I suspect that these names are names in the report and this can create
problems because of the sequence how the report is processed. Using the
actual data field names from the underlying data source will correct the
problem.
Hope this helps.
Fons
:
I am using the following statements, but they return nothing (blank), when
they should be returning -18.96
=IIf([BillingTotal] = < 0,"Val",[BillingTotal])
=IIf(Val([BillingGST]-[BillingQST])<0,Val([BillingGST]-[BillingQST]),"")
Can someone tell me what is wrong with my formulas?
Thanks in advance for your help
Jeannie
