D
Duane Hookom
No. You can't sum or aggregate "controls" from one section to another. If
"txt1" is the name of a control in the detail section, you can't use
=Sum(txt1) in another section.
--
Duane Hookom
MS Access MVP
--
"txt1" is the name of a control in the detail section, you can't use
=Sum(txt1) in another section.
--
Duane Hookom
MS Access MVP
--
Ana said:Duane,
Let's start a new report.
In Detail we add a new text box (txt1) with control ='X'
Then in the footer section we add
=Sum(IIf([txt1]='X';1;0))
Shouldn't the results be 1?
Ana said:I used what you suggested me to. Last one being:
=Sum(Abs([tiempo]='TEST1'))
It does,t have to be in Spanish. Access converts it automatically. (a
nice feature)
Duane Hookom said:I still don't see anywhere in your previous posts, the exact control
sources you have tried in your text boxes in your report footer section.
I would expect to see something like:
=Sum(Abs([tiempo]='TEST1'))
I don't know what the Spanish version of Abs() is....
--
Duane Hookom
MS Access MVP
--
Duane,
The Detail part of the report has 4 text boxes which display an X when
the conditions are met.
The columns are name as 0 - 3 days 3- 5 days and so on. This part
is working nicely.
The footer will have 4 text boxes with its relative sum, which at the
moment is not working and that's what we are trying to resolve.
I noticed that makes no differance between IIF and IIf. Both cases are
translated into the Spanish version (SiInm) when the file is saved w/o
a problem.
"Duane Hookom" <[email protected]> escribió en el mensaje
How can three text boxes have this expression?
=IIF([tiempo]='TEST1';'X';''), =IIF([tiempo]='TEST2';'X';''),
=IIF([tiempo]='TEST3';'X';''), =IIF([tiempo]='TEST4';'X';'')
What you provided looks like either 4 controls sources with a comma
between or a single expression that wouldn't work. None of this looks
like the control sources in the sample report you sent. Also, I would
expect the IIF() would be IIf() with a lower-case "f".
You are continuing to add to my confusion.
--
Duane Hookom
MS Access MVP
--
Ok. Sorry.
Here's the sp as is w/o traslating into english.
The fields used in the Detail part of the Report are:
CONCESIONARIO_NOMBRE, SOLICITUD_ID, ESTADOST_NOMBRE and 3 text boxes
with the following expression:
=IIF([tiempo]='TEST1';'X';''), =IIF([tiempo]='TEST2';'X';''),
=IIF([tiempo]='TEST3';'X';''), =IIF([tiempo]='TEST4';'X';'')
"Duane Hookom" <[email protected]> escribió en el mensaje
I wish you would provide an accurate reply each time. I don't know
what your SP looks like. I also don't know what field names are
returned or what the exact expressions you use in your report
controls.
--
Duane Hookom
MS Access MVP
--
CASE WHEN .... THEN .... ELSE 'DoNothing'.... END AS TEMP
Yes, the dates are legitimate as are stored in the table as default
values.
The sp works fine on its own.
"Duane Hookom" <[email protected]> escribió en el
mensaje Does the stored procedure display on its own? I'm not sure why you
seem to have and extra "WHEN." in your sp. Also, I would expect to
see an "ELSE" in there somewhere:
CASE WHEN .... THEN .... ELSE .... END AS TEMP
Do DATE_ON and DATE_OFF always have legitimate values?
--
Duane Hookom
MS Access MVP
--
It was my error; I didn't change it before sending it. It's
*temp* in both cases.
Temp comes from a stored procedure:
CASE WHEN DATEDIFF(D ; CAST(DBO.MYTABLE.DATE_ON AS SMALLDATETIME)
; CAST(DBO.MYTABLE.DATE_OFF AS SMALLDATETIME)) BETWEEN 0 AND 3
THEN 'TEST1' WHEN. END AS TEMP
DATE_ON and DATE_OFF are nvarchar (8) and the dates are stored as
yyyymmdd.
It could be a data type problem as you mentioned.
Ana
"Duane Hookom" <[email protected]> escribió en el
mensaje It's not appropriate to attach files to messages in news groups.
I would have accepted a file off line to avoid this.
It's difficult to trouble shoot when one control source in the
detail section is:
=IIf([tiempo]="TEST2",1,0)
and the control source in the report footer is:
=Sum(Abs([temp]="TEST1"))
Without seeing a field list or data types, I can't tell if you
made a mistake or if the expressions are legitimate.
--
Duane Hookom
MS Access MVP
--
Thanks for your patience, Duane.
I tried both of your suggestions with no avail. I'm attaching a
copy of the report if it can be of help.
Thanks again.
"Duane Hookom" <[email protected]> escribió en el
mensaje "'not valid expression' " is not an error that I have ever
seen in a report
control. You have never replied back with the actual
expression you are
entering. I have given you several different options.
Also, what section of your report are you using this
expression?
--
Duane Hookom
MS Access MVP
--
Thanks again Duane.
I tried the expression in a new report (just in case) but it
still gives
me the same error. How about if I count the Xs as text said
someting like
=Sum(IIf([text1]='X';1;0))? This isn´t working either!?!
"Duane Hookom" <[email protected]> escribió en el
mensaje
If you had copied and pasted your expression in your reply,
I would have
expected to see IIf( not IIF(. I also wanted you to reply
with your
summary expression from the "bottom of the column". This
should be
something like:
=Sum(IIf([Temp]="TEST1";1;0)
Otherwise try:
=Sum(Abs([Temp]="TEST1"))
This should work in the Control Source of a text box in a
Report or Group
Header or Footer.
--
Duane Hookom
MS Access MVP
--
Well, I'm using Acc03 Spanish and Sql2k English.
The information in Details comes from a stored procedure
and I'm using
the expression =IIF([Temp]='TEST1';"X";"") in a text box to
pain an X.
The variables come from the statement Case When...Then...
AS Test1 End
AS Temp. My problem is to sum the Xs at the bottom of the
column.
"Duane Hookom" <[email protected]> escribió en
el mensaje
Where are you using this expression and what is your exact
attempt. I
would have used
=Sum(IIf([Temp]="TEST1",1,0))
but assumed you aren't using the same language or locale
as I am.
--
Duane Hookom
MS Access MVP
--
Thanks Duane but I'm getting an 'not valid expression'
error.
"Duane Hookom" <[email protected]> escribió
en el mensaje
=Sum(IIF([Temp]='TEST1';1;0))
--
Duane Hookom
MS Access MVP
--
Hi,
I use the following expression to mark an X on a report
line in
Details:
=IIF([Temp]='TEST1';"X";"")
In the page footer, I'd like to add the Xs. How would I
go by?
TIA
Ana