problems with Sum() and DSum() in reports

[Eric6]

Hi,
i'm having problems with the Sub and DSub method in my reports.
everytime i use it to calculate the total of a field that is not in the detail section, the Sum returns the total of every record in my recordsource instead of the total of my subgroup

do you know what i could be doing wrong?
DSum does the same thing..

If i'm not being clear enough just say it and i'll try to explain it better
I really need this to work

Thanks
Eric

 

[Eric6]

no one has a clue?

 

[StarPassing]

Using Dsum, sounds like you are not passing the criteria. With out the criteria, you get it all.

To get a record-by-record total and a grand total, check out the help on RunningSum Property.

Tom
Been there, done that.

 

[Rolliee]

Send code to get answers. No OUIJI boards allowed.

 

[Eric6]

There is not code for my report
i'll try to draw the layout of my report in design view
everything in all caps is a variable

==Project Header============================================
Project: PROJECT
==Name Header=============================================
Consultant: NAME                          Per Diem:  PERDIEM
                                          Budgeted: BUDGETED
==Detail==================================================
                                          MONTH         COST
==Name Footer==============================================
                        Total:                     SUM(COST)
                        Balance:      (BUDGETED)-(SUM(COST))
==Project Footer============================================
                        Total Budgeted:        SUM(BUDGETED)
                        Total Spent:               SUM(COST)
                        Balance:     SUM(BUDGETED)-SUM(COST)
==Report Footer=============================================
Total perdiem: SUM(PERDIEM) 
Total Budgeted: SUM(Budgeted) 
Total Spent: SUM(COST)
Total Balance: SUM{BUDGETED)-SUM(COST)
============================================================
[code]
My problem is
Every SUM method calculates based on every record in the report's recordsource (a query) instead of calculating the values only contained in its subgroup (ie: Name Footer)

this is very bad because the budgeted and perdiem amount appear everytime there is an entry under a name...
for example, my query is results are:
NAME          PERDIEM   BUDGETED   COST
Doe, John     $100      $10,000    $550
Doe, John     $100      $10,000    $350
Doe, John     $100      $10,000    $750
Bob, Billy    $150      $25,000    $600  
Bob, Billy    $150      $25,000    $400

the REPORT FOOTER would say that:
the total perdiem is $600 when its actually $250
the budgeted amount is $80,000 when its actually $35,000
the total cost are ok...

I tried to change the name of the control in the report and i tried using the new name to make sure SUM() would use the values in the report instead of the query but when i try to run the report, i got pop-up boxes asking for the values of the controls who's name i just changed...
I find that very odd.
For example, if i were to change the name of COST (only the name, not the control source) from COST to COST1,
i would get a pop-up box asking for the value of COST1 when i try to run the report.

I know this is a long post but 
i think i've explained everything...
basically, i need to be able to calculate totals based on groups. 

so i thought DSUM would fix my problems but when
i tried to use DSUM() with a criteria
to something  like  DSUM("COST", "CnsltReport", "Project = '" &me.project &"'") 
and i try to run the report, i get a pop-up box asking for the value of 'me' ?!?!!?
i cant use 'me.' in a report?

Please help, 
like i said, i really need this to work

thank you in advance