conditional sum @ report footer

[kolorasta]

I have a report... i want to sum only some rows of the report's recordsource...

example:
Report's recordsource:
"SELECT importe, iva FROM factura"

I want now a textbox in the report's footer with the sum of all "importe" where "iva=0,21"

how can i do that? That to I have to put in the control source of the textbox?

thanks and sorry for my poor english. (i hope to be clear)

 

[lameid]

You mean you want the result of the following as the footer?

Select Sum(Importe)
From factura
Where Iva IN(0,21)

If so,

Report Record source:
Select Importe, iva, IIF(IVA IN(0,21),Importe,0) AS ImporteX
From factura

Report footer:
Text box
Control Sourece = Sum(ImporteX)


If not, please be more specific.

 

[kolorasta]

thanks... i will try that... i think this solves my problem..

 

[dhookom]

You could try add a text box to the report footer with a control source of:
=Sum(Abs(iva=0,21) * importe)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kolorasta]

Both work fine.. i think the =Sum(Abs(iva=0,21) * importe)
is more flexible because it does not depend of the recordsource of the report... but i don't know if it is faster than the other solution...

Both are valid.. thanks dhookom and lameid

 

[lameid]

dhookom's solution should be faster. A very clever solution. The only thing worth mentioning is that it relies on a boolean test returning 0 for false and -1 or 1 for true (Access returns -1). If you were to move the backend to some database backend that behaved differently (doubtful), you would have trouble and would have to either use my method or adapt this one.

If this is not a concern, I'd go with dhookom's solution.

 

[lameid]

Scratch my concern. The logic is in the report so it is Access running the test no matter what. If it were in the query, it would be a valid concern. My mind is a little slow from not getting enough sleep... Sorry for rambling something incorrect.

 

[lameid]

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rs.Open "YourTable, Query Or SQL Statement", cnn, adOpenDynamic, adLockOptimistic
While Not RS.EOF

DoCmd.OpenReport "ReportName", acViewNormal, , "UniqueField = " & RS!UniqueField
RS.Movenext
Wend


The thing to note here is that if UniqueField is text or a date you will have to include the delimeters in the criteria...

Text:
DoCmd.OpenReport "ReportName", acViewNormal, , "UniqueField = """ & RS!UniqueField & """"
Date:
DoCmd.OpenReport "ReportName", acViewNormal, , "UniqueField = #" & RS!UniqueField & "#