DSum from Query problems

[Eric28]

I have a query that selects records from a certain machine on a certain date. The records include [Weight Out] and [Shift] fields. I would like to be able to sum the weight produced by each shift (1-3). I am putting a text box in the Report footer with the control source: DSum("[Weight Out]","Daily Report","[Shift]='1'&quot with "Daily Report" being the query that I am using. I am getting #error when I run the report.

I created a pie graph that calculates it for me, but I want to be able to place it elsewhere in the report (without the graph). The row source for the pie graph (that does work) is: SELECT [Shift],Sum([Weight Out]) AS [SumOfWeight Out] FROM [Daily Report] GROUP BY [Shift];

Why doesn't the DSum work?

 

[scriverb]

First of all the DSum Function can't be used in a report like you have done. It was designed to sum up a field with a particular Domain such as a queries recordset.

In a report you have a text control in the Detail section that has as its Control source the field that you want to sum the values.[Weight Out]

What you need to do now is Open your Sort and Grouping window by clicking that button at the top of the screen in report Design view or selecting it from the View Menu. You now must create a Sort on the Shift field from the dropdown box. Identify it as Ascending to the right and indicate Yes to the Group Header and Footer items below.

Now the report has two extra sections, one above and one below the Detail Sections of your report. In the one above(Group Header) you can put a control identifying the [Shift] field from your query. In the Section below(Group Footer) create a control below the column of the [Weight Out] control. Line them up on the right and stretch it to the left a little past the size of the Detail Section control. Now the Control Source property of that control should read:
=Sum([Weight Out])
You can add text to the Section to indicate what this represents but basicially now for each shift the value displayed here will be the Sum of all of the Detail [Weight Out] controls for the particular shifts.

Good luck and get back with me if you have any other questions. Bob Scriver

 

[Eric28]

Thanks for the help!! It now sums them up... I just have to play around with the formatting.

 

[scriverb]

Great. Glad to be of help getting you started in the right direction. Bob Scriver