RE: Subtract on a report based on QRY cross table

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

"Duane Hookom" wrote:

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


"Pedro" wrote:

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

"Duane Hookom" wrote:

"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


"Pedro" wrote:

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

"Duane Hookom" wrote:

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


"Pedro" wrote:

I've already send what i meant to your email.
Hope you can help
Tks a lot
Pedro

"Duane Hookom" wrote:

I am clueless. Do you have some table structures and sample data as well as
the desired output?

--
Duane Hookom
Microsoft Access MVP


"Pedro" wrote:

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?

"Duane Hookom" wrote:

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


"Pedro" wrote:

Hi everyone
I have a report based on a qry cross table. It's about a balance *** 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...
.

Quantcast