PEDRO, forget that I even mentioned any colors. They were just sample data
values. They had no meaning. FORGET THEM. I tried to look at your sample JPG
to determine what field and what value can be used to determine if the
[2008-01] should be added or subtracted. That's where I came up with "62221".
Look at the jpg and you will see this value on the far left.
I only want to know what field and value in your report should I look at to
determine if a record value should be added or subtracted.
--
Duane Hookom
Microsoft Access MVP
:
Duane,
You've been most pacient with me and this problem and finaly this formula is
working without errors, =Soma([2008-01])*Ise([ContaN]="Verde";-1;1)
but it doesn't subtract but still summing, even i change green to red or
with portuguese words cose of my os and ms language.
Any ideas? Please feel free to let me know if you prefer to send you the mdb
file.
Many thanks again
Regards
Pedro
:
You were supposed to substitute your field name for AcctNum and your field
value for "Green". I expect it would be something like:
=Soma([2008-01] * IIf(AcctNum = "62221";-1;1) )
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane and tks for your answer
I don't know what you mean by Red and Green values as well as that AcctNum
function but i did try your formula like you said =Soma([2008-01]) *
IIf((AcctNum = "Green";-1;1))
and it's not working. It says something like wrong number of arguments about
this function...
Can you help, again?
Many thanks
Pedro
:
"First" and "Second" don't have much value in SQL or aggregate functions.
Assume you have two records with unique AcctNum values of "Red" and "Green".
If you want to subtract the Green [Fld1] from the Red [Fld1] in a report
footer section, you would use a text box with a control source of:
=Sum([Fld1] * IIf(AcctNum = "Green",-1,1))
--
Duane Hookom
Microsoft Access MVP
:
I'm sorry. I thought i was clear about that.
Ok. On that report made with a cross tab query, and a subreport also with a
cross tab query, this one, the sub report, i would like, on Total, to show
the first line subtracting the second line, instead of summing. This
subreport only has 2 lines of values.
I hope you can help me.
Tks
Pedro
:
It doesn't help unless you explain what you mean by "instead a sum, a
subtraction between only those 2 lines".
--
Duane Hookom
Microsoft Access MVP
:
I've already send what i meant to your email.
Hope you can help
Tks a lot
Pedro
:
I am clueless. Do you have some table structures and sample data as well as
the desired output?
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane
Actualy i have a field with only 2 possible values : debit or credit. But,
what i would like is to subtract both records on my crosstab report ...
Any suggestions?
:
I'm not exactly sure what you want but I think I would start by adding a
PlusMinus field in "a table with those accounts only with groups number" that
will store either 1 or -1.
--
Duane Hookom
Microsoft Access MVP
:
Hi everyone
I have a report based on a qry cross table. It's about a balance sheet of a
company, where i only want some account number to analyse. For that, i've add
a table with those accounts only with groups number so i can have the sum of
those on the report. So far so good.
Now i want to add, like subreport, a report that shows me only the values of
2 accounts and instead of sum, i would like to subtract. They're always 2
accounts only.
Question : how can i subtract those 2 values on a qry cross table to show
them on my report as a subreport?
Tks in advance
Pedro
P.S. The query cross table shows the account number, description of the
account and the value for Jan, Feb, Mar, etc...
