Summering på grupper på rapport

Hi Hugo
The sum function can also take parameters and because you dont give it any it does what you ask, returns the sum of fldPriceTotal (all)

So you could have a date field on the form and get you Sun function to use the date field, or even better, build the SQL for the report dynamically

OK - give me just a minute to check that

Terry->"The sum function can also take parameters"!
Det har jeg da aldrig set (heller ikke, når jeg slår op i hjælpen)

Kan du give et konkret eksempel?

=Sum([fldPriceTotal];???)

How are you setting the filter?
If you open the repeort using for example.
DoCmd.OpenReport stDocName, acPreview, , "ID = 1"

then your filter should work and you should only get the sum for the one record

I think thats my mistake! I'm obviosuly getting mixed up with dsum, sorry Hugo!

have you tried using the wizard for adding sum fields to your report? If you group on one or more fields the get an extra button "Summary options" which allows you to choose fields you want to sum on

For some reason I have always had trouble getting some wizards to run when I am working on an .adp database as this one is.

I am buildig the filter on a form (  fldOrderDate = ' & me.cboDate & "'" ) and applying this to the report on its OnOpen event. And it works just fine except for the sum :-)

Try opening the report including the filter when you open i

DoCmd.OpenReport stDocName, acPreview, , "fldOrderDate = '" & me.cboDate & "'"

That doesn't work either - I wil have to go into 'think mode' and try to figure out a way do do this.  I know from earlier that filters and .adp/SQL can give some unexpected results.  I have made some solutions earlier where I have built a view in VBA, saved that on the SQL server and used that as reportsource, but this report uses to many tables.

Cant you make a stored procedure with the necessary parameters and use this for the report?

That could also be a solution, but I have never been good friends with stored procedures and parameters i Access reports :-)

Hmm. der er i hvert fald én anden metode du kunne prøve. Da du alligevel sætter filteret op fra din form inden du åbner rapporten, burde den virke.

Lav en selvstændig forespørgsel baseret på den du normalt ville bruge til rapporten:

SELECT * FROM Rapportdata;

Baser nu rapporten på denne forespørgsel i stedet.

Hvor du før satte rapportens filter op, ændrer du i stedet nu i SQL strengen denne forespørgsel udfører. Hvis nu du havde kaldt denne forespørgsel FiltreredeRapportdata, kunde du skrive:

Currentdb.QueryDefs("FiltreredeRapportdata").SQL = "SELECT * FROM Rapportdata WHERE fldOrderData = '" & me.cboDate & "'"

Herefter ka' du åbne rapporten. Det skulle undre mig meget om ikke sumtal passer nu, for her er det jo i hvert fald hele datagrundlaget der bliver filtreret på.

Jeg havde helt glemt dette spørgsmål da jeg fandt en løsning dagen efter mit sidste indlæg.
Jeg har hele tiden brugt en forespørgsel som grundlag da der hentes data fra flere tabeller og der er et par beregnede felter i viewet også (pris * ordreantal f.eks.)
Jeg gør så det at jeg på rapportens On_Open event laver en SQL streng f.eks.
strSQL = "SELECT * FROM " & conQuery_Nierle_Report & " WHERE fldOrdered = 0"
Jeg sætter så rapportens recordsource til at være lig med denne streng
Me.RecordSource = strSQL

Så spiller det som det skal.

Ja, ja, sådan er det når man glemmer at aflyse brylluppet. Man risikerer, at der dukker gæster op, som vil bespises! :-)

Hej Hugo,

Lukker du så ikke lige?

pfh tak :)

Jo det gør jeg